Site icon ExcelNotes

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

If you want to count the number of cells based on the text that is part of the cell and non-case-sensitive, please check here. The following formula counts the number of cells with a certain text string in the cells and is 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 contain a text string with case sensitive from their names.

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

=SUMPRODUCT(–ISNUMBER(FIND("Leo",A2:A12)))
=SUMPRODUCT(ISNUMBER(FIND("Leo",A2:A12))*1)

In the example, eight people have names with "Leo". However, only four of them are "Leo" that are case-sensitive (A3 and A5).

Please note that "leo" and "LeO" should not include because they are not exactly the 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 add.
– Array2 is optional; the second array is to multiply and add.

Exit mobile version