CODEWITHSUNDEEP
excelformula
Teiv
Teiv

Reputation: 2635

How to COUNTIFS with an array of criteria and match NOT condition?

enter image description here

My data table is like the image above. I can easily count the number of Male participants in group B or C using this array formula:

=SUM(COUNTIFS($B:$B, $E3, $C:$C, $F3:$F4))

The result is 3 as expected. However I'm gonna do the reverse thing, that is count the number of Male participants in NOT group B or C. The result should be 1 but currently I'm stuck at this.

Can anybody show me a way please (preferably not just counting the number of all Male participants and then do a subtraction)? I have even tried to change the values in the Group to something like <>B and <>C but it just doesn't work.

Upvotes: 2

Views: 26621

Answers (2)

barry houdini
barry houdini

Reputation: 46361

As you only have 2 in the group you can easily use COUNTIFS with 2 separate criteria, i.e.

=COUNTIFS($B:$B,$E3,$C:$C,"<>"&$F3,$C:$C,"<>"&$F4)

but clearly that might not be desirable for a large group, so you could use SUMPRODUCT like this to reference the group once

=SUMPRODUCT(($B:$B=$E3)*ISNA(MATCH($C:$C,$F3:$F4,0)))

ISNA will exclude matching rows - to include use ISNUMBER

You can replace F3:F4 with any single row or column of values

Note: whole columns with SUMPRODUCT will work (post Excel 2003) but is undesirable as Jerry says

Upvotes: 4

Jerry
Jerry

Reputation: 71558

Hmm, the thing with the formula right now is that the first COUNTIFS (for F3) will return 2 and the second COUNTIFS (for F4) will return 3, which SUM converts into 5 when you try:

=SUM(COUNTIFS($B:$B, $E3, $C:$C, "<>"&$F3:$F4))

I would suggest using SUMPRODUCT instead:

=SUMPRODUCT(($B:$B=$E3)*($C:$C<>$F3)*($C:$C<>$F4))

And maybe make the range smaller since this can take some time (you don't need to insert this as an array formula).

Otherwise, another option would be to count all the Males, and then subtract the counts for group B and subsequently C:

=COUNTIF($B:$B, $E3)-SUM(COUNTIFS($B:$B, $E3, $C:$C, $F3:$F4))

Upvotes: 2

Related Questions