How to Count Cells that are a Text String (Case Sensitive)

If you want to count the number of cells based on the text that is part of the cell, please check here. The following formula is to count the number of cells with the text string for full text in the cells and case sensitive.

Example: The employee's name is in column A, the position is in column B, and the employees' salaries are in column C. You need to get the count of cells that are a text string with case sensitive from their names.

Formula: To get the count of cells that are Leo (case sensitive)

=SUMPRODUCT(--EXACT("Leo",A2:A12))

The EXACT function is case-sensitive. If a cell is "Leo", the result returns true. Otherwise, it returns false.

The double hyphens are to convert true to 1, and false to 0.

The SUMPRODUCT function returns the sum of the products of the new array.

In the example, eight people have the name with "Leo". However, only two of them are "Leo" with case sensitive (A3 and A5).

Please note that "leo" and "LeO" should not include because they are not exactly same as "Leo". To get the count of cells that are not case sensitive, please check here.

Notes: The SUMPRODUCT function

The SUMPRODUCT function adds all the multiplication results for all arrays.

Formula:

=SUMPRODUCT(array1, [array2], …)

Explanations:

– Array1 is required, the first array is to multiply and then add.
– Array2 is optional, the second array is to multiply and then add.

Leave a Reply