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 is to count the number of cells that have a certain text string 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 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)))

  • The Find function is case-sensitive. If a cell includes "Leo", the result returns 1. Otherwise, it returns an error.
  • The ISNUMBER function returns the numbers into true, and others into false.
  • The double hyphens are to convert true to 1, and false to 0.

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 then add.
– Array2 is optional, the second array is to multiply and then add.

Leave a Reply