Case Sensitive Count

by | Jul 2, 2018 | Excel Tips & Tricks

How to count values by case sensitivity

Case sensitive count can not be handled easily using standard count formulas like COUNTIF or COUNTIFS. In this article, we'll shot you how you can count case sensitive data by using a combination of SUMPRODUCT and EXACT functions.

Syntax

=SUMPRODUCT(--(EXACT(G3,$E$3:$E$10)))

Steps

  1. Start with =SUMPRODUCT(
  2. Type double minus characters to convert Boolean to numbers --
  3. Continue with EXACT(
  4. Select the reference that contains value to count G3,
  5. Select the reference that contains the range to be searched $E$3:$E$10
  6. Type )) and press Enter to close both functions and finish the formula

How

The regular COUNTIF or COUNTIFS functions don't support for case sensitivity as the other statistical functions in Excel. However, with SUMPRODUCT and EXACT functions, we can mimic case sensitive count. The EXACT function compares 2 strings and returns a Boolean value according to if they are "exactly" the same or not. In short, the EXACT function is case sensitive. There are 3 tricks we will use here:

  1. First, make the EXACT function return an array which is handled automatically when we defined our formula as an array formula.
  2. Second, convert TRUE/FALSE values into 1/0 by double minus characters.
  3. Finally, sum 1/0 values to count TRUE values in the array returned by the EXACT function. Note that 0 values are neutral in a sum operation.

Let's analyze the formula.

EXACT(G3,$E$3:$E$10) formula returns {TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE} array for "CHARMANDER" value. By wrapping the formula with the SUMPRODUCT function, we ensured the evaluation of EXACT function for each cell in $E$3:$E$10 range.

If the SUMRODUCT function has a single argument, it sums the values in the argument. Because of TRUE/FALSE values are not additive, we need to convert them to numbers. Double minus characters will do the trick. TRUE and FALSE values become 1 and 0 respectively if there are -- characters in front of the array.

Alternatively, the N function can do the same trick as well.

As a result; --EXACT(G3,$E$3:$E$10) returns {1;0;0;1;0;1;0;0} array.

Summing up 1 and 0 gives the count of the matched values because 0's are neutral.

=SUMPRODUCT(--EXACT(G3,$E$3:$E$10))

Also see related articles how to sum values with OR operator using SUMPRODUCT with multiple criteria, how to calculate weighted average with SUMPRODUCT.