Please check here if you want to count the number of cells based on the text that is part of the cell. The following formula is to count the number of cells with the text string for full text in the cells and case sensitive.
Example: You are working with a worksheet with the employee's name in column A, the position in column B, and the employees' salaries in column C.
Formula: How many cells are with case-sensitive Leo?
=SUMPRODUCT(–EXACT("Leo", A2:A12))
=SUMPRODUCT((EXACT("Leo", A2:A12))*1)
- Step 1: EXACT("Leo", A2:A12): The EXACT function is case-sensitive. If a cell is "Leo", the result returns true; otherwise, it returns false;
- Step 2: The double hyphen (or times one) is to convert true to one and false to zero;
- Step 3: The SUMPRODUCT function returns the sum of the products of the new array.
In the example, eight people have the name "Leo". However, only two are "Leo" with case-sensitive (A3 and A5).
The formula does not count "leo" and "LeO" because they are not 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.