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)
- The Find function is case-sensitive. If a cell includes "Leo", the result returns one; 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 one and false to zero;
- The SUMPRODUCT function returns the sum of the products of the new array.
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.
