Count cells that end with

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.