If you count the number of cells with numbers, please use the COUNT function. To count the number of cells that start with a case-sensitive text string, you can use the SUMPRODUCT function and the EXACT function.
Example: You are working on a database with the names in column A, and the salary in column B.
Formula 1: To count how many people have names starting with "Liam" (case sensitive)
=SUMPRODUCT(–EXACT("Liam",LEFT(A2:A14,LEN("Liam"))))
The EXACT function is case-sensitive. When the first four letters match the letters of "Liam", it returns to true; otherwise, it returns to false.
The double hyphens (‐‐) convert true into 1 and false into 0.
There are two people's names starting with "Liam": Liam Hudson and Liam DAVIS.
Formula 2: The text "Liam" can be a reference cell, for example, if "Liam" is in cell D3, please use the formula below:
=SUMPRODUCT(–EXACT(D3,LEFT(A2:A14,LEN(D3))))
The formula above is case-sensitive (e.g., Liam, not LIAM). To check the count of the cells starting with a noncase-sensitive test string, please check here.
Note: The SUMPRODUCT function
The SUMPRODUCT function adds all the multiplication results for all arrays.
Formula:
=SUMPRODUCT(array1, [array2], [array3], …)
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.
– Array3 is optional, the third array is to multiply and then add.