How to Count Cells that End with a Text String

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.

Count Cells End with a Text String

Leave a Reply