How to Count Cells Ending A Text String

If you count the number of cells with numbers, please use the COUNT function. To count the number of cells that end with a text string, you can use the COUNTIF 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"

= COUNTIF(A2:A8, "*SON")

The wild card asterisk (*) stands for "all letters" in front of "SON".

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

The formula above is not case-sensitive. To check the count of the cells ending with a case-sensitive test string (e.g., SON, and not Son), please check here.

Formula 2: To count how many people's names ending with "mas" and have 9 letters in total

= COUNTIF(A2:A8, "??????MAS")

The wild card question mark (?) stands for "one letter". To have 9 letters and end with "MAS", you need 6 question marks in the formula. The wild card of an asterisk (*) represents all the letters, while the question mark (?) represents only one letter.

There is one person's name ending with "MAS" and 9 letters in total.

Note: The COUNTIF function

Formula:

= COUNTIF(Range, "*Text"), where the asterisk (*) stands for all other letters.

Explanations:

– The range is required, the range of cells that you want to count;

– "*Text" is required, a text string you look for; an asterisk (*) is the wild card for all other letters.

Count Cells End with a Text String

Leave a Reply