COUNTIFS - Count all values in a range that equal any value in another range.

**HeebieGeebie** · 06-12-2013, 06:10 AM

Morning all!

The title basically descirbes what I'm trying to achieve - I want to count all values in a range that equal any value in another range.

So I have a massive table of about 40,000 rows, and I'm counting certain colmumns based on multiple criteria. Here's one of the formulas im using:

=COUNTIFS(Faults!$L$2:$L$100537,">="&$D3,Faults!$L$2:$L$100537,"<"&$D4,Faults!$A$2:$A$100537,L$3,Faults!$J$2:$J$100537,L$4,Faults!$I$2:$I$100537,DataVal!N$5:N$24)

This formula is created dynamically with VBA depending on user input, so if possible i'd like to keep it as a 'COUNTIFS' function.

The problem is with this last bit:

Faults!$I$2:$I$100537,DataVal!N$5:N$24

It only counts values equal to the first cell in the range N5:N24, and not the whole range.

Any help would be greatly appreciated!

Cheers,

Adam

**Ace_XL** · 06-12-2013, 06:25 AM

Try SUMPRODUCT instead

=SUMPRODUCT(--(Faults!$L$2:$L$100537>=$D3),--(Faults!$L$2:$L$100537<$D4),--(Faults!$A$2:$A$100537=L$3),--(Faults!$J$2:$J$100537=L$4),--(ISNUMBER(MATCH($I$2:$I$100537,Dataval!$N$5:$N$24,0))))

**daddylonglegs** · 06-12-2013, 09:05 AM

Hello Adam,

When you use a 20 cell range as a criteria value the result is a 20 element array, but you only see the first value (the sum for N5). You need SUM or SUMPRODUCT wrapped around your COUNTIFS to sum that array, I prefer SUMPRODUCT as it avoids "array entry", i.e.

=SUMPRODUCT(COUNTIFS(Faults!$L$2:$L$100537,">="&$D3,Faults!$L$2:$L$100537,"<"&$D4,Faults!$A$2:$A$100537,L$3,Faults!$J$2:$J$100537,L$4,Faults!$I$2:$I$100537,DataVal!N$5:N$24))

Note: this gives you the normal efficiency benefits of COUNTIFS, SUMPRODUCT is only used to sum the resulting array