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.
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 criteriaargument: 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:
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.
In this example, the goal is to count cells in the range B5:B16 that begin 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...
In this example, the goal is to count cells that contain a specific substring. This problem can be solved with the SUMPRODUCT function or the COUNTIF function. Both approaches are explained below. The SUMPRODUCT version can also perform a case-...
COUNTIF is an Excel function to count cells in a range that meet a single condition. COUNTIF can be used to count cells that contain dates, numbers, and text. The criteria used in COUNTIF supports logical...
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.