COUNTIFS breaking when used with multiple column range

Hi All,

This one is confounding me.

I have a sheet with four columns: Date; test 1; test 2; test 3

The date column contains dates... all cells in the test columns contain either "safe", "at-risk" or "-"

I am trying to make a COUNTIF formula that counts the total quantity of "safe" occurrences in a date range, and a second formula that counts the total quantity of "at-risk" occurrences in a date range.

My COUNTIF works if I the criteria are date> X and date <= Y and a B:B (or other single column) = "safe", but when I try to count across all the test columns (B:D) I end up with a #VALUE error.

In the attached sheet, you can see the multi column count working in G2, the single column formula working in H2 and the combination failing in I2.

This one has me stumped and any help would be much appreciated!

Welcome to the forum.

You'll need to switch to SUMPRODUCT:

=SUMPRODUCT(($A2:$A52>I1)*($A2:$A52<=J1)*(B2:C52="safe"))