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.
