Explanation
In this example, the goal is to count codes in a case-sensitive way. The COUNTIF function and the COUNTIFS function are both good options for counting text values, but neither is case-sensitive, so they can’t be used to solve this problem. The solution is to use the EXACT function to compare codes and the SUMPRODUCT function to add up the results.
EXACT function
The EXACT function’s sole purpose is to compare text in a case-sensitive manner. EXACT takes two arguments : text1 and text2. If text1 and text2 match exactly (considering upper and lower case), EXACT returns TRUE. Otherwise, EXACT returns FALSE:
=EXACT("abc","abc") // returns TRUE
=EXACT("abc","ABC") // returns FALSE
=EXACT("abc","Abc") // returns FALSE
Worksheet example
In the example shown, we have four codes in column D and some duplicated codes in B5:B15, the named range data . We want to count how many times each code in D5:D8 appears in B5:B15, and this count needs to be case-sensitive. The formula in E5, copied down, is:
=SUMPRODUCT((--EXACT(D5,data)))
Working from the inside-out, we are using the EXACT function to compare each code in column D with data (B5:B15):
--EXACT(D5,data)
EXACT compares the value in D5 (“ABC”) to all values in B5:B15. Because we are giving EXACT multiple values in the second argument, it returns multiple results. In total, EXACT returns 11 values (one for each code in B5:B15) in an array like this:
--{TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE}
Each TRUE represents an exact match of “ABC” in B5:B15. Each FALSE represents a value in B5:B15 that does not match “ABC”. Because we want to count results, we use a double-negative (–) to convert TRUE and FALSE values into 1’s and 0’s. The resulting array looks like this:
{1;0;0;0;0;0;0;1;1;0;1} // 11 results
Using the double-negative like this is an example of Boolean logic . Now that we have an array of 1’s and 0’s, the only remaining task is to sum things up.
SUMPRODUCT function
SUMPRODUCT is a versatile function that appears in many formulas because of its ability to handle array operations natively in older versions of Excel. The array created in the previous step is delivered directly to the SUMPRODUCT function:
=SUMPRODUCT({1;0;0;0;0;0;0;1;1;0;1}) // returns 4
With just one array to process, SUMPRODUCT sums all numbers in the array and returns the final result: 4.
Explanation
In this example, the goal is to count the values in column B listed in the range E5:E7. One way to do this is to give the COUNTIF function all three values in the named range things (E5:E7) as criteria, then use the SUMPRODUCT function to get a total. The formula in G4 is:
=SUMPRODUCT(COUNTIF(B5:B15,things))
The COUNTIF function counts the number of cells in a range that meet criteria. When you give COUNTIF a range of cells as the criteria, it returns an array of numbers as the result, where each number represents the count of one thing in the criteria range. In this case, the named range things (D5:D7) contains 3 values, so COUNTIF returns 3 results in an array as shown below:
=COUNTIF(B5:B15,things)
=COUNTIF(B5:B15,{"apples";"pears";"kiwis"})
={2;3;1} // result from COUNTIF
Since “apple” appears twice, “pears” appears three times, and “kiwis” appears once, the array contains the numbers 2, 3, and 1. This array is returned directly to the SUMPRODUCT function :
=SUMPRODUCT({2;3;1})
With a single array to process, SUMPRODUCT simply sums the array and returns 6.
With an array constant
With a limited number of values, you can use an array constant in your formula like this:
=SUMPRODUCT(COUNTIF(B5:B15,{"apples","pears","kiwis"}))
ISNUMBER and MATCH
The above formula works fine, but has some limitations due to the nature of COUNTIF . As an alternative, you can use the formula below, which uses the ISNUMBER function with the MATCH function to achieve the same result:
=SUMPRODUCT(--ISNUMBER(MATCH(B5:B15,things,0)))
This is a more flexible formula in cases where logical conditions become more complex . It’s also useful when you need to extract a value from a range in the data to use in a condition.
