Explanation
In this example, the goal is to count rows in a set of data using multiple criteria and “not equals to” logic. Specifically, we want to count males that are not in group A or B. All data is in an Excel Table named data in the range B5:D15. This problem can be solved with the COUNTIFS function or the SUMPRODUCT function. Both approaches are explained below.
COUNTIFS function
The COUNTIFS function returns the count of cells that meet one or more criteria, and supports logical operators (>,<,<>,=) and wildcards (*,?) for partial matching. Conditions are supplied to COUNTIFS in the form of range/criteria pairs — each pair contains one range and the associated criteria for that range:
=COUNTIFS(range1,criteria1,range2,criteria2,etc)
In this case, the first condition is that Gender is Male:
=COUNTIFS(data[Gender],"Male") // returns 6
The result is 6, since there are six Males in the table. Next, we need to exclude group “A”:
=COUNTIFS(data[Gender],"Male",data[Group],"<>A")
This formula returns 5. Notice we use the not equal to operator (<>) enclosed in double quotes. Finally, we need to exclude group B with another range/criteria pair:
=COUNTIFS(data[Gender],"Male",data[Group],"<>A",data[Group],"<>B")
Notice the syntax to exclude group B is the same and both conditions use the same range. This formula returns 2. The COUNTIFS function joins all criteria with AND logic, so it works well to exclude A and B in this case. As more exclusions are added however, the syntax gets more cumbersome, because each new exclusion requires another range/criteria pair. The SUMPRODUCT option below scales more easily.
SUMPRODUCT function
Another way to solve this problem is with the SUMPRODUCT function and Boolean logic . To start off, we can count males like this:
=SUMPRODUCT(--(data[Gender]="Male")) // returns 6
Working from the inside out, this expression tests all values in the Gender column for “Male”:
data[Gender]="Male"
Since there are 11 cells in the column, the result is an array with 11 TRUE and FALSE values:
{TRUE;FALSE;TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE}
Each TRUE represents a “Male” in the Gender column. SUMPRODUCT will ignore TRUE and FALSE values by default, so we need to convert these TRUE and FALSE values to their numeric equivalents, 1 and 0. A simple way to do this is with a double negative (–):
--(data[Gender]="Male")
The result from this snippet is an array like this:
{1;0;1;0;1;1;0;1;0;1;0}
Notice the TRUE values are now 1s. This array is delivered to SUMPRODUCT, which returns 6:
=SUMPRODUCT({1;0;1;0;1;1;0;1;0;1;0}) // returns 6
Next, we need to exclude groups “A” and “B”. A good way to do this is with the MATCH function together with the ISNA function like this:
ISNA(MATCH(data[Group],{"A","B"},0))
Notice the configuration of MATCH is “reversed”. The lookup_value is given as data[Group] , and the lookup_array is given as the array constant {“A”,“B”}. 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:
{1;2;#N/A;1;2;#N/A;1;2;1;2;#N/A}
This array has 11 rows, like the data table. 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. To convert this array into something we can use, we use the ISNA function:
ISNA(MATCH(data[Group],{"A","B"},0))
The result from ISNA is an array of TRUE and FALSE values like this:
{FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE}
ISNA returns TRUE only for the #N/A errors, so the TRUE values in this array indicate rows where the group was not “A” or “B”. If we wanted to use this expression in SUMPRODUCT by itself, we would use a formula like this:
=SUMPRODUCT(--ISNA(MATCH(data[Group],{"A","B"},0)))
The double negative (–) again converts TRUE and FALSE values, and the result looks like this:
=SUMPRODUCT({0;0;1;0;0;1;0;0;0;0;1}) // returns 3
The result is 3, since there are 3 records not in group A or B.
Putting it all together
The next step is to put both tests above together inside SUMPRODUCT like this:
=SUMPRODUCT((data[Gender]="Male")*ISNA(MATCH(data[Group],{"A","B"},0)))
Notice we use multiplication (*) to join the two expressions. We do this because multiplication corresponds to AND logic in Boolean 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. The formula evaluates like this:
=SUMPRODUCT({1;0;1;0;1;1;0;1;0;1;0}*{0;0;1;0;0;1;0;0;0;0;1})
=SUMPRODUCT({0;0;1;0;0;1;0;0;0;0;0})
=2
The final result is 2 since two males are not in Group A or B.
Note: In Excel 365 and Excel 2021 you can use the SUM function instead of SUMPRODUCT if you prefer. This article provides more detail .
Explanation
In this example, the goal is to count numbers in the range B5:B15 ( named data ) where the third digit is a specific number, indicated in column D. You might think the COUNTIF function would be a good way to solve this problem. However, for reasons explained below, COUNTIF won’t work. Instead, you can use the SUMPRODUCT and Boolean logic . See below for a full explanation.
COUNTIF function
The COUNTIF function returns the count of cells that meet one or more criteria, and supports logical operators (>,<,<>,=) and wildcards (,?) for partial matching. You would think you could use the COUNTIF function with the question mark (?) and asterisk () wildcards to count numbers where the third digit is 1 like this:
=COUNTIF(data,"??1*") // returns 0
However, COUNTIF will return zero. The problem is that using any wildcard in criteria means that COUNTIF will interpret the pattern as a text value, whereas the values in column B are numeric . As a result, COUNTIF will never find a matching number and the result will always be zero. As a workaround, you might try the trick below to coerce the numbers in data to text by concatenating an empty string ("") to the range like this:
=COUNTIF(data&"","???1") // throws error
However, this will cause Excel to throw the generic “There’s a problem with this formula” error, so it’s not possible to even enter the formula. This happens because COUNTIF is in a group of eight functions that require an actual range for range arguments. This means you can’t use an array operation to modify the range argument inside COUNTIF, COUNTIFS, SUMIF, SUMIFS, etc.
SUMPRODUCT function
Another way to solve this problem is with the SUMPRODUCT function , the MID function , and Boolean logic . To count numbers in data where the third digit is 1, we can use SUMPRODUCT like this:
=SUMPRODUCT(--(MID(data,3,1)="1")) // returns 2
Working from the inside out, the MID function is used to extract and test the third digit from the numbers in data like this:
MID(data,3,1)="1"
Because we give MID 11 numbers in the range B5:B15, MID returns an array with 11 results:
{"3";"4";"5";"2";"3";"2";"4";"1";"3";"3";"1"}="25"
Note that MID automatically converts the numbers to text, so we use the text value “1” for the comparison. The result is an array of TRUE and FALSE values:
{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE}
In this array, TRUE values correspond to numbers where the third digit is 3. We want to count these results, but first we need to convert the TRUE and FALSE values to 1s and 0s. To do this, we use a double negative (–).
--{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE}
The resulting array contains only 1s and 0s and is delivered directly to the SUMPRODUCT function:
=SUMPRODUCT({0;0;0;0;0;0;0;1;0;0;1})
With only a single array to process, SUMPRODUCT sums the items in the array and returns 2 as result. In this formula, note we are hardcoding the value “1” and we set num_chars to 1 inside the MID function. To adapt the formula for the worksheet shown, we a reference to cell D5 like this:
=SUMPRODUCT(--(MID(data,3,1)=D5))
As the formula is copied down, it returns the count of numbers in data where the third digit equals the numbers in column D. Note that the numbers in column D are entered as text values , since the result from MID will also be text. You can avoid this requirement by adapting the formula to coerce the value in D5 to text:
=SUMPRODUCT(--(MID(data,3,1)=D5&""))
Here we concatenate an empty string ("") to the D5, which will convert a numeric value to text. This version of the formula will work when there are numbers in column D, or text values.
Note: In Excel 365 and Excel 2021 you can use the SUM function instead of SUMPRODUCT if you prefer. This article provides more detail .
