Site icon ExcelNotes

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: How many people's names end with "SON"?

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

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

Two people's names end 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: How many people's names end with "mas" and have nine letters?

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

The wild card question mark (?) stands for "one letter". To have nine 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.

One person's name ends with "MAS" and has nine 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

Exit mobile version