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.