Multiple Criteria ranges within a Countifs statement

0
votes

I have criteria ranges (C3:C9, I4:I9, O4:O9) which I would like to use in my countifs statement. Doing one range on its own works as expected. But as soon as I add more than one range into the countif statement it returns a NULL value.

Due to the layout of the spreadsheet these values are the same just split across 3 ranges.

Screeshot of the excel spreadsheat

The spreadsheet should help me view times teams are going on lunch. And i have a working model. Just need help working with Multiple Data ranges in Countifs

Tried using a single Data set - Works as expected

Tried all sets singular. They also work as intended. 

=COUNTIFS($C4:C$9, "<=" & A12,$E4:$E9,">" & A12) Works Single Criteria Range)

Works Single Criteria Range)

=COUNTIFS($C4:C$9 $I$4:$I$8 $O$4:$O$9, "<=" & A12,$E4:$E9,">" & A12)

NULL Value Does not work.

The Expected output would be for all data ranges to be accepted and not return a null value.

1
excel

1 Answers

0
votes

You might want to consider using SUMPRODUCT(), here is an example:

enter image description here

The formula I used translates to:

=SUMPRODUCT(((A1:A11<=A12)*(A1:A11<>"")*(C1:C11>A12))+((D1:D11<=A12)*(D1:D11<>"")*(F1:F11>A12)))

You can extend the formula with more ranges obviously. In your case it would look like:

=SUMPRODUCT(((C4:C9<=A12)*(C4:C9<>"")*(E4:E9>A12))+((I4:I9<=A12)*(I4:I9<>"")*(K4:K9>A12))+((O4:O9<=A12)*(O4:O9<>"")*(Q4:Q9>A12)))