Exceljet

Quick, clean, and to the point

Count cells equal to case sensitive

Excel formula: Count cells equal to case sensitive
Generic formula 
=SUMPRODUCT((--EXACT(value,range)))
Explanation 

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:

=SUMPRODUCT((--EXACT("ayako",B3:B9)))

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

=SUMPRODUCT((--EXACT(D3,names)))

Both formulas 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:

{FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE}

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:

{0;0;0;0;1;1;1}

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.

 

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.