Countifs with multiple OR criteria ranges

Question

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?

Answer 1

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.

Answer 2

It sounds like you want to sum multiple countifs. If so, there are multiple ways to do this. A way that is easy to adapt or change is the following.

1. Are the ranges independent? For example, items in the I column do not impact any of the other columns so make a name for just that range.

Example Formula [1] =countifs(Data'!$I:$I,UK!$I$3) call this UK_I (use the name manager on the formula ribbon. make a new name) Continue this same format where make a name for each independent data range.

2. Do two or more conditions need to be true for it to count? Need both month and year to be correct. Make a countifs that has 2 ranges.

Example formula [2] = Countifs('Data'!$O:$O,MONTH(UK!K$5), 'Data'!$P:$P,YEAR(UK!K$5)) name this UK_K

3. Once have all the elements, sum(UK_I,UK_K,etc.....) Allows the ability to sum them up.

This is easy to adjust once have it working by modifying the appropriate name or adding a new name to add additional data.