Array as criteria in Excels COUNTIFS function, mix

Question

This question already has an answer here:

• Countif With Multiple OR Criteria 4 answers

I have been googling this for a while and can´t seem to get it to work. I use Excel 2010 and want to count rows on a mix of AND and OR operators.

What I want to do is something like this

COUNTIFS($A:$A,"string1" , $B:$B,"string2" , $C:$C,{"stringA","stringB","stringC"})

This means mixing both AND and OR operator in the COUNTIFS function. Col A and col B must match the string criteria but col C must only match one of the values in the array given as criteria. Match on colA AND colB AND on one array value in col C.

A different approach would be to create one COUNTIFS function for each value in the array like

COUNTIFS($A:$A,"string1" , $B:$B,"string2" , $C:$C,"stringA") + COUNTIFS($A:$A,"string1" , $B:$B,"string2" , $C:$C,"stringB") + COUNTIFS($A:$A,"string1" , $B:$B,"string2" , $C:$C,"stringC")

This however is a lot of duplicate code and that bugs me! The logic solution would be to pass an array as criteria for column C. Also my array contains more then three values...

When I do this in Excel the formula is accepted and a few rows are counted but the results are way to low. It is not the result I´m expecting.

Any Excel-Pro out there that can tell me if this is possible? It would save me a lot of work! Thanks!

Answer 1

you can use

=SUM(COUNTIFS($A:$A,"string1",$B:$B,"string2",$C:$C,{"stringA","stringB","stringC"}))

you can also use a similar array construction with sumproduct

=SUMPRODUCT(($A:$A="string1")*($B:$B="string2")*($C:$C={"stringA","stringB,"stringC"}))

Answer 2

Hmm, well, 'or' does make things longer. See a related question. I guess you could use =SUMPRODUCT() but still have it a bit long. Being an array function though, you'll have to use Ctrl + Shift + Enter.

=SUMPRODUCT(($A:$A="string1")+0,($B:$B="string2")+0,((($C:$C="stringA")+($C:$C="stringB")+($C:$C="stringC"))>0)+0)

Disclaimer: I haven't tested this yet.