Hi Gary

C7-7 will only give you a date. What is the criteria that you want applied to the result? A certain date/month/year etc?

What are the actual formulas that you had tried? It would help if you can attach some sample.

Sunny

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 All

Thanks for the replies to date.

I have attached a copy of the original spreadsheet I am trying to rework and tidy up.

The issue I am trying to fix is the result of the formula in Cell H5 which refers to the date populated in Cell G6.

Kind Regards

Gary

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

Hi Anders

Thanks very much. I will check further and Yes it is what I am trying to achieve with a COUNTIFS statement.

My goal was to improve an old spreadsheet that had been created a long time ago by someone else.

Kind Regards

Gary

Hi Anders

I have used the formula you provided and it works well.

Thank you very much.

Regards, gary