Formula to count if 2 conditions occur

**Stufft** · 06-16-2016, 06:06 AM

I have a sheet with 2 columns of dates "Date opened" and "Date closed"

not all lines have a date closed entry.

I'm trying to count how many cases have been opened longer than 18 months and are still open.

=COUNTIF('Tracker'!F6:F1582,"<"&TODAY()-"545")

The above gives me the count of cases with date opened older than 545 days ago (18 months approx).
If I use filters I can see that there are 8 cases of which 3 are still open (ie don't have a date in the closed column)

How do I get the formula to count the blanks or subtract the non-blanks from the new count?

**Lemice** · 06-16-2016, 06:08 AM

Hello Stufft,

Welcome to the forum

Available from Excel 2007 onward is COUNTIFS, where it's COUNTIF with more condition to it, for example

=COUNTIFS('Tracker'!F6:F1582, "<"&TODAY()-"545", Range 2, "<>""")

Where it will count the number of date opened older than 545 days ago, and the date in Range 2 is not blank.

Available for all Excel version is SUMPRODUCT, you can use it like a COUNTIFS on any version