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.
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: 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: 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
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.
