Count cells equal to case sensitive

An excel formula to count cells equal to case sensitive

Related Functions

 Count cells equal to case sensitive

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.


0 votes. 0 / 5