Summary

To count the number of cells that end with specific text, you can use the COUNTIF function with a wildcard. In the example shown, the formula in cell E5 is:

=COUNTIF(data,D5)

where data is the named range B5:B16. COUNTIF returns 3, since there are three cells that end with "R". Note that COUNTIF is not case-sensitive. 

Generic formula

=COUNTIF(range,"*text")

Explanation 

In this example, the goal is to count cells in the range B5:B16 that end with specific text, which is provided in column D. For convenience, the range B5:B16 is named data

COUNTIF function

The simplest way to solve this problem is with the COUNTIF function and a wildcard. COUNTIF supports three wildcards that can be used in the criteria argument: question mark (?), asterisk(*), or tilde (~). A question mark (?) matches any one character and an asterisk (*) matches zero or more characters of any kind. The tilde (~) is an escape character to match literal wildcards that may appear in data. In this example, we mainly use an asterisk (*). To count cells in a range that end with "apple" you can use a formula like this:

=COUNTIF(range,"*apple) // end with "apple"

In the worksheet shown, we use the criteria in column D directly like this:

=COUNTIF(data,D5)

As the formula is copied down, COUNTIF returns the count of cells in data (B5:B16) that end with the text seen in D5:D8, which already includes the wildcard(s) needed. Notice that COUNTIF is not case-sensitive. Cell D6 contains a lowercase "y", yet COUNTIFS happily matches the uppercase "Y"s in B5:B16.

Below are the formulas in the worksheet, altered to include the criteria from column D directly:

=COUNTIF(data,"*R") // returns 3
=COUNTIF(data,"*y") // returns 2
=COUNTIF(data,"*002??-?") // returns 5
=COUNTIF(data,"*-?") // returns 12

Notice the last two formulas use both question mark (?) and asterisk (*) wildcards. The ? wildcard matches any one character, so you can use it to create criteria that is more specific than with just the * wildcard. Notice the hyphen (-) is hardcoded to make the pattern even more specific.

Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.