Count cells equal to case sensitive
=SUMPRODUCT((--EXACT(value,range)))
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.
