Where data is the named range B5:B15. The result is a case-sensitive count for each code in column D.

EXACT function

The EXACT functions 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:

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: Working from the inside-out, we are using the EXACT function to compare each code in column D with data (B5:B15): 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: 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: 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: With just one array to process, SUMPRODUCT sums all numbers in the array and returns the final result: 4. Note: Because SUMPRODUCT can handle arrays natively, it’s not necessary to use Control+Shift+Enter to enter this formula. In Excel 365, you can use the SUM function instead of SUMPRODUCT. To read more about this, see Why SUMPRODUCT?

Dave Bruns

Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.