Countifs with multiple OR criteria ranges

microsoft excelworksheet-function

I am using the formula:

=SUMPRODUCT(
COUNTIFS(
'Data'!$I:$I,UK!$I$3,
'Data'!$O:$O,MONTH(UK!K$5),
'Data'!$P:$P,YEAR(UK!K$5),
'Data'!$D:$D,$A13,
'Data'!$E:$E,**$C13:$F13**
)
)

My problem is that is if I try to add another range criteria such as, 

'Data'!$A:A,**$C14:$F14**

the formula ends up producing a 0 result instead of a number. This is not the correct result. Is there a way to have multiple 'OR' ranges in a single countifs formula?

Best Answer

To do multiple OR statements, one must be vertical and the other horizontal.

So use transpose on the second:

=SUMPRODUCT(
COUNTIFS(
'Data'!$I:$I,UK!$I$3,
'Data'!$O:$O,MONTH(UK!K$5),
'Data'!$P:$P,YEAR(UK!K$5),
'Data'!$D:$D,$A13,
'Data'!$E:$E,$C13:$F13,
'Data'!$A:$A,Transpose($C13:$F13)
)
)

The limit is two such arrays in the criteria, one horizontal and one vertical. 3 or more can not be done without splitting the formulas and summing them.

Also try to use SUM() instead of SUMPRODUCT. In theory it should work with SUM as a regular formula.

Related Question