To count the number of cells that end with a text string, you can use the COUNTIF function.
Formula:
= COUNTIF(Range, "*Text"), where 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.
Cautions:
The COUNTIF function can count the range of cells with a single criterion. Please refer to COUNTIFS function when you have 2 or more criteria to count.
Wild card Asterisk (*) stands for "all other letters", while Question Mark (?) stands for "one letter".
Example 1: To count how many people having the names end with "SON"
– Range: Column A (A2:A8)
– "*Text" (criteria): End with "SON" with or without other letters in front, "*SON".
= Countif(A2:A8, "*SON")
There are 2 people having the names end with "SON".
Example 2: To count how many people's names that end with "mas" and have 9 letters in total
– Range: column A (A2:A8)
– "???TEXT" (criteria): cells end with "mas" and 9 letters in total, that is "??????MAS"
= Countif(A2:A8, "??????MAS")
There are 1 person's name ending with "MAS" and 9 letters in total.
