Count cells equal to case sensitive

Count cells equal to case sensitive

Related Functions

Sponsored Link


If you need to count cells that contain certain text in a case-sensitive manner, you can use a formula that uses the EXACT function along with SUMPRODUCT.

In the example, there is a list of names in a named range (“names”), B3:B9. In the range D3:D6, there is a another list of names, with duplicates removed. To get the count of “ayako”, you can use the formula:


In the example, which uses the named range “names”, the first formula is:


Both formula above return 3, since there are 3 occurrences of “ayako” (lowercase) in the range B3:B9.

How this formula works

The EXACT function takes to arguments, text1 and text2. When these values match exactly (respecting case), EXACT returns TRUE. In this case, we are giving EXACT a range for text1, so it becomes an array formula and returns an array of TRUE and FALSE values like this:


Each TRUE represents an exact match of “ayako” in B3:B9.

The double-hypen (technically: double unary) converts the TRUE false values into 1’s and 0’s that look like this:


Finally, SUMPRODUCT simply adds up the values in the array and returns 3. Because SUMPRODUCT can handle arrays natively, it’s not necessary to use Control+Shift+Enter to enter this formula.


Sponsored Link

0 votes. 0 / 5

Excel - Excel Functions - Excel Formulas