How to Count Partial Matches in a Range Using COUNTIF (with Wildcards)
This article explains how to count partial matches in Excel. By using the COUNTIF function with wildcards, you can easily count data that partially matches a specific string.
What Are Wildcards?
Wildcards are symbols that represent any characters in a string. Combined with the COUNTIF function, they allow you to count data that partially matches a condition. The two main wildcards are:
- * (asterisk): Represents any number of characters.
- ? (question mark): Represents a single character.
Basic Usage of the COUNTIF Function
COUNTIF counts the number of cells in a range that meet a specified condition. The basic syntax is:
=COUNTIF(range, condition)
For example, if you want to count how many cells in the range A1:A10 contain the word “apple,” use the following formula:
=COUNTIF(A1:A10, “*apple*”)
This formula counts the number of cells in the range A1:A10 that contain the word “apple” (even as part of another word).
Example: Counting Partial Matches
The following table demonstrates how to count partial matches. The list contains the names of fruits.
| A | B | C | |
|---|---|---|---|
| 1 | Apple | =COUNTIF(A1:A5, “*apple*”) | |
| 2 | Banana | ||
| 3 | Apple Juice | ||
| 4 | Orange | ||
| 5 | Cherry |
Steps
- In cell B1, enter the following formula:
- =COUNTIF(A1:A5, “*apple*”)
- This formula counts the number of cells in the range A1:A5 that contain “apple”.
- Press Enter, and the result will appear in cell B1.
Result
When you enter this formula, cell B1 displays “2”. This is because “Apple” (A1) and “Apple Juice” (A3) both contain “apple”.
Using Other Wildcards for Partial Matches
Here’s how to use other wildcards for counting partial matches.
Using the Question Mark (?) for Partial Matches
The “?” wildcard represents a single character. For example, you can count matches for “rin?” to include strings like “ringo” and “rinka” (one character difference).
| A | B | C | |
|---|---|---|---|
| 1 | Apple | =COUNTIF(A1:A5, “Appl?”) | |
| 2 | Banana | ||
| 3 | Appla | ||
| 4 | Orange | ||
| 5 | Cherry |
This formula counts “Apple” (A1) and “Appla” (A3). As a result, cell B1 displays “2”.
Using Partial Match for a “Yes/No” Check
You can also use partial matches in an IF function to display “Yes” or “No” based on whether a match exists:
=IF(COUNTIF(A1:A5, “rin?”)>0, “Yes”, “No”)
This formula displays “Yes” if a partial match exists within the specified range.