Quick, clean, and to the point

Count cells that end with

Excel formula: Count cells that end with
Generic formula 

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:


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. 


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:


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

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.