Using Countifs with Date Range and multiple qualifier

Brent Allen

I"m trying to create a formula, referencing another sheet, with qualifier of date range, and 'Status' being 'Live' OR 'Ended'.

I have this:

=COUNTIFS({Test Start Date}, <=DATE(2020, 1, 31), {Test Start Date}, >=DATE(2020, 1, 1), {Status}, "Live", {Status}, "Ended")

returning #INVALID REF

Thoughts?

TIA

Brian W

There is a problem with your formula in that you are checking for the Status of Live AND Ended. It should be something like this:

=COUNTIFS({Test Start Date}, <=DATE(2020, 1, 31), {Test Start Date}, >=DATE(2020, 1, 1), {Status}, OR(@cell = "Live", @cell = "Ended"))

However, the #INVALID REF sounds like a problem with your reference. Double check your ranges and that the sheet reference names match exactly.

Brian W

There is a problem with your formula in that you are checking for the Status of Live AND Ended. It should be something like this:

=COUNTIFS({Test Start Date}, <=DATE(2020, 1, 31), {Test Start Date}, >=DATE(2020, 1, 1), {Status}, OR(@cell = "Live", @cell = "Ended"))

However, the #INVALID REF sounds like a problem with your reference. Double check your ranges and that the sheet reference names match exactly.

Brent Allen

Thank you Brian W. Much appreciated