Specifically, we want to count males that are not in group A or B.
All data is in anExcel Tablenameddatain the range B5:D15.
This problem can be solved with the COUNTIFS function or the SUMPRODUCT function.
Both approaches are explained below.
Next, we need to exclude group “A”:
This formula returns 5.
Notice we use the not equal tooperator(<>) enclosed in double quotes.
This formula returns 2.
As more exclusions are added however, the syntax gets more cumbersome, because each new exclusion requires anotherrange/criteriapair.
The SUMPRODUCT option below scales more easily.
SUMPRODUCT function
Another way to solve this problem is with theSUMPRODUCT functionandBoolean logic.
We do it this way to keep the rows in the output array consistent with the table.
The result from MATCH is an array like this:
Thisarrayhas 11 rows, like thedatatable.
The numbers indicated rows where group “A” or “B” were found.
The #N/A errors indicate rows where group “A” or “B” were not found.
We do this because multiplication corresponds to AND logic inBoolean algebra.
Also notice that we no longer need the double negative (–).
This is because the math operation of multiplication automatically converts the TRUE and FALSE values to 1s and 0s.
MATCH supports approximate and exact matching, andwildcards(* ?)
COUNTIF can be used to count cells that…
