COUNTIF using OR Criteria with a cell reference to dates

[seaside_escape]

I am trying to count the number of rows/records which had 'open' notes on a variable date, which involves checking for:

• A Closed Date in the future
• OR
• No Closed Date at all (blank cell)

There are other criterions which are straight forward and included in the COUNTIF, but I'm having trouble trying to get COUNTIF to recognise the Date in the future (Greater Than) because it uses a cell reference in the array, rather than a hard coded date manually typed into the formula.

I'm trying to avoid using COUNTIFS()+COUNTIFS() because the formula will get quite large and I'm hoping there must be a way of using the array part that will pick up a cell reference?

My current formula is:
{=IF(SourceSelected="A",SUM(COUNTIFS(Data[Notes on Case?],"Yes",Data[Received Date],"<="&D27,Data[Closed Date],{">&D27",""})),"")}

The formula is correctly picking up the Received Date less than or equal to cell D27 (the variable date), and also the Closed Date which is blank (""), but it doesn't pick up the Closed Date greater than cell D27 (the variable date). I think this is because it is enclosed in speech marks so COUNTIF thinks it's looking for a text value of ">&D27".

How can I get the array to recognise ">&D27" as "greater than cell D27" so that I can still use this handy OR element in the COUNTIF?

[leelnich]

Hi Seaside- Unfortunately, Excel doesn't let you use cells in arrays. As you say, it's reading that reference as TEXT, so the test won't work. Try this:

Please Login or Register  to view this content.

BTW, I used SUMPRODUCT so you could enter this normally. No CSE required (I think...haven't tested it).

Please click the Add Reputation star below any helpful posts, and use Thread Tools (up top) to mark your thread as SOLVED once you have your answer. Thanks!-Lee

[xlnitwit]

Perhaps this
=IF(SourceSelected="A",SUM(COUNTIFS(Data[Notes on Case?],"Yes",Data[Received Date],"<="&D27,Data[Closed Date],TEXT(D27,{""">""0",""}))),"")

[seaside_escape]

SUMPRODUCT works perfectly, thank you very much.

[leelnich]

You're most welcome! If complete, please mark your thread as SOLVED (Thread Tools up top). Thanks -Lee

Please click the Add Reputation star below any helpful posts, and use Thread Tools (up top) to mark your thread as SOLVED once you have your answer. Thanks!-Lee

[seaside_escape]

Perhaps this
=IF(SourceSelected="A",SUM(COUNTIFS(Data[Notes on Case?],"Yes",Data[Received Date],"<="&D27,Data[Closed Date],TEXT(D27,{""">""0",""}))),"")

This formula works too, until I input more "OR" elements, for example, I also want to include any rows/records which don't have a Complaint or the Complaint was raised after the date in D27;
=IF(SourceSelected="A",SUM(COUNTIFS(Data[Notes on Case?],"Yes",Data[Received Date],"<="&D27,Data[Closed Date],TEXT(D27,{""">""0",""}),Data[Complaint Date],TEXT(D27,{""">""0",""}))),"")

The result brought back is not the same volume when I do a manual count/filter on the dataset. SUMPRODUCT does seem to be able to cater for the extra OR part:
=IF(SourceSelected="A",SUMPRODUCT((Data[Notes on Case?]="Yes")*(Data[Received Date]<=D27)*((Data[Closed Date]>D27)+(Data[Closed Date]=""))*((Data[Complaint Date]>D27)+(Data[Complaint Date]=""))),"")

[xlnitwit]

You need to alter the orientation of the second array if you use a second set of criteria
=IF(SourceSelected="A",SUM(COUNTIFS(Data[Notes on Case?],"Yes",Data[Received Date],"<="&D27,Data[Closed Date],TEXT(D27,{""">""0",""}),Data[Complaint Date],TEXT(D27,{""">""0";""}))),"")

[seaside_escape]

You need to alter the orientation of the second array if you use a second set of criteria
=IF(SourceSelected="A",SUM(COUNTIFS(Data[Notes on Case?],"Yes",Data[Received Date],"<="&D27,Data[Closed Date],TEXT(D27,{""">""0",""}),Data[Complaint Date],TEXT(D27,{""">""0";""}))),"")

Yes, that now works too. Brilliant.

Thank you xlnitwit and leelnich for your speedy help on this. I have marked this as SOLVED.

[xlnitwit]

You're welcome.