Count cells equal to case sensitive

Count cells equal to case sensitive

Related Functions

Sponsored Link

=SUMPRODUCT((--EXACT(value,range,D3)))

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:

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

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

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

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:

{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.

 

Sponsored Link

0 votes. 0 / 5

Excel - Excel Functions - Excel Formulas
Logo