Explanation
In this example, the goal is to count cells equal to a specific value. In this case, we want to count cells that contain “red” in the range D5:D16. This problem can be solved with the COUNTIF function and the SUMPRODUCT function, as explained below.
COUNTIF function
One way to solve this problem is with the COUNTIF function, which is designed to count cells in a range that meet one specific condition. COUNTIF takes two arguments : range and criteria :
=COUNTIF(range,criteria)
For this problem, range is D5:D16 and criteria is “red”. We place double quotes (“red”) because “red” is a text value . The formula in cell H6 is:
=COUNTIF(D5:D16,"red") // returns 4
COUNTIF returns the count of values in D5:D16 that are equal to “red”, which is 4.
Note: when text values are supplied directly as criteria in COUNTIF, they need to be enclosed double quotes (""). If you have a criteria in another cell, supply the cell address as criteria without quotes, as seen in this example .
With SUMPRODUCT
Another way to solve this problem is with the SUMPRODUCT function and Boolean logic . To count cells in the range D5:D16 that are equal to “red”, you can use a formula like this:
=SUMPRODUCT(--(D5:D16="red")) // returns 4
Because the range D5:D16 contains 12 values, the logical expression D5:D16=“red” returns an array with 12 TRUE/FALSE results like this:
{TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE}
Each TRUE corresponds to a cell in D5:D16 that contains “red”. The double negative (–) is used to convert the TRUE and FALSE values to 1s and 0s:
=SUMPRODUCT({1;1;0;0;0;0;1;0;0;1;0;0})
SUMPRODUCT then sums the items in the array and returns 4 as a final result. Although in this example the COUNTIF function is an excellent solution, SUMPRODUCT provides a lot more flexibility in more complex problems .
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.
