How to Count Cells Ending A Text String (Case Sensitive)

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 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 ending with "SON" (case sensitive)

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

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 (‐‐) convert true into 1 and false into 0.

There are two people's names ending with "SON": Aiden JOHNSON and Jack TAISON.

Formula 2: The text "SON" can be a reference cell, for example, if "SON" is in cell D3, please use the formula below:

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

The formula above is case-sensitive (e.g., SON, and 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 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.

Leave a Reply