If you count the number of cells with numbers, please use the COUNT function. To count the number of cells that end with a case-sensitive text string, you can use the SUMPRODUCT and EXACT functions.

**Example: **You are working on a database with the names in column A and the salary in column B.

**Formula 1:** How many people's names **end with "SON"** (case sensitive)?

=SUMPRODUCT(–EXACT("SON",RIGHT(A2:A16,LEN("SON"))))

=SUMPRODUCT(EXACT("SON",RIGHT(A2:A16,LEN("SON")))*1)

Two names end with "SON": Aiden JOHNSON and Jack TAISON.

The EXACT function is case-sensitive. When the last three letters match the letters of "SON", it returns to true; otherwise, it returns to false;

The double hyphens (or times one) convert true into one and false into zero;

**Formula 2:** The text "SON" can be a reference cell. For example, if "SON" is in cell D3, you can use the formula below:

=SUMPRODUCT(–EXACT(D3,RIGHT(A2:A16,LEN(D3))))

=SUMPRODUCT(EXACT(D3,RIGHT(A2:A16,LEN(D3)))*1)

The formula above is case-sensitive (e.g., SON, not Son). To check the count of the cells ending 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 add.

– Array2 is optional; the second array is to multiply and add.

– Array3 is optional; the third array is to multiply and add.