Count Cells with an AND condition, or another alternative|General Excel Questions & Answers|Excel Forum|My Online Training Hub

Hello Gary,

There must be something wrong in your data or more likely, your file.
As I try to show with below picture, with the criterias you mention it works fine using COUNTIFS.

As you can see, the date in cell C2 is text, still COUNTIF and COUNTIFS take that text date as a date, removes 7 days and looks for date 2018-03-01 when counting. So even when "wrong" it works fine. I am using Excel from Office 365 subscription, I can't tell if there is a difference with other versions.

Good luck with finding the error. Try using the Error Checking -->Trace Error tool in Formulas tab.

Br,
Anders

Hi Gary,

I have checked your file and the array formula you have in cell H5 works. In case you want to change and use COUNTIFS then you can use following formula.

=COUNTIFS($H$11:$H$65502,"Done",$G$11:$G$65502,">"&$G$6-7)

The result will be 4, as there are four rows that have status "Done" and have a due date greater than February 8, 2018.
Not sure if this what you are after, but I have based it on the current array formula in cell H5.

Br,
Anders