Yes, Excel’s COUNTIF supports wildcards: use * to match any sequence of characters, ? to match a single character, and ~ to treat a wildcard as a literal character. For example, =COUNTIF(A2:A10,"*apple*") counts every cell in A2:A10 that contains the word “apple” anywhere in the text.
How Wildcards Work in COUNTIF
COUNTIF is designed to count cells that meet a single condition. When you put a wildcard inside the criteria text, Excel treats it as a pattern rather than a literal string. The three wildcard characters are:
*(asterisk) – matches any number of characters, including zero."*ed"matches “red”, “needed”, and “ed”.?(question mark) – matches exactly one character."b?g"matches “bag”, “big”, and “bug”, but not “bg” or “beag”.~(tilde) – escapes the next character, so you can search for a literal*,?, or~."*~?*"finds any cell that contains a question mark.
Wildcards work in COUNTIF, COUNTIFS, SUMIF, SUMIFS, AVERAGEIF, and AVERAGEIFS. They also work in the Find and Replace dialog (Ctrl+F on Windows, Cmd+F on Mac) and in Excel tables’ filter search boxes.
Basic Examples with Text
Assume you have a list of product names in A2:A20. Here are typical patterns and their results:
=COUNTIF(A2:A20,"*phone*")– counts cells containing “phone” anywhere (e.g., “smartphone”, “phone case”, “landline phone”).=COUNTIF(A2:A20,"phone*")– counts cells starting with “phone” (e.g., “phone charger”, but not “smartphone”).=COUNTIF(A2:A20,"*phone")– counts cells ending with “phone” (e.g., “smartphone”, but not “phone case”).=COUNTIF(A2:A20,"???")– counts cells with exactly three characters (e.g., “pen”, “ink”).=COUNTIF(A2:A20,"*~**")– counts cells that contain at least one literal asterisk. The first*matches anything,~*matches a literal asterisk, and the final*matches anything after it.
Note that COUNTIF is case-insensitive: "*apple*" matches “Apple”, “APPLE”, and “apple”. If you need case-sensitive counting, use SUMPRODUCT with the EXACT function instead.
Counting with Multiple Criteria (COUNTIFS)
COUNTIFS allows multiple conditions across different columns, and each condition can use wildcards independently. For instance, with names in A2:A20 and status in B2:B20:
=COUNTIFS(A2:A20,"*Smith*",B2:B20,"Active")
This counts rows where column A contains “Smith” and column B equals “Active”. The same wildcard rules apply to each criteria pair. You can mix wildcard and exact-match criteria freely.
Common Pitfalls and How to Avoid Them
Several mistakes trip up users when using wildcards with COUNTIF:
- Forgetting the asterisk for partial matches:
"apple"without asterisks only matches cells that are exactly “apple”, not “apple pie” or “pineapple”. - Using wildcards with numeric criteria: Wildcards only work with text. If your criteria is a number like
5, COUNTIF treats it as a numeric value, not a pattern. To match numbers that contain a digit, you must first convert them to text or use a helper column with TEXT(). - Matching empty cells:
"*"matches any non-empty cell, but it does NOT match truly empty cells. To count empty cells, use=COUNTIF(range,"")or COUNTBLANK. - Literal tilde: If you want to count cells that contain a tilde, you need to escape it as
~~. - Case sensitivity: As noted, COUNTIF ignores case. If case matters, use SUMPRODUCT with EXACT.
Alternatives to COUNTIF with Wildcards
When wildcards get too restrictive, other functions offer more power:
- SUMPRODUCT with ISNUMBER and SEARCH: This gives case-insensitive partial matching and can handle arrays. For example, to count cells in A2:A20 containing “apple” (case-insensitive):
=SUMPRODUCT(--ISNUMBER(SEARCH("apple",A2:A20))). SEARCH supports wildcards too, but it’s more flexible for multiple conditions. - FILTER with wildcards (Excel 365): You can filter rows based on a wildcard and then count the rows:
=ROWS(FILTER(A2:A20,ISNUMBER(SEARCH("apple",A2:A20)))). This works in Excel 365 and Excel 2021. - COUNTIFS with multiple wildcard patterns: If you need to count cells matching either “apple” or “orange”, you can use
=COUNTIF(A2:A20,"*apple*")+COUNTIF(A2:A20,"*orange*"), but beware of double-counting cells that contain both words. To avoid that, use SUMPRODUCT with OR logic.
Frequently Asked Questions
How do I count cells that contain a specific word anywhere?
Use =COUNTIF(range,"*word*"). The asterisks on both sides mean “any characters before and after”. This counts cells like “word”, “wordy”, “crossword”, and “a word here”.
Can I use wildcards with numbers?
No, wildcards only work with text. If your numbers are stored as text (e.g., with leading zeros), wildcards work. For actual numbers, you cannot use * or ? to match digits. You’d need to convert numbers to text using TEXT() in a helper column or use SUMPRODUCT with TEXT().
How do I count cells that do NOT contain a certain text?
Use the not-equal operator with a wildcard: =COUNTIF(range,"<>*apple*"). This counts all cells that do not contain “apple”. Be careful: it also counts empty cells. If you want to exclude blanks, use =COUNTIFS(range,"<>*apple*",range,"<>"").
Why does my COUNTIF with a wildcard return 0?
Check three things: (1) Ensure the criteria is in quotes, e.g., "*apple*" not *apple*. (2) Verify the range includes the cells you think it does – a common mistake is including a header row that doesn’t match. (3) Confirm the text is not in a merged cell or formatted as a number. Also, remember that wildcards are case-insensitive, so “Apple” and “apple” match.
How do I count cells with a literal question mark?
Use a tilde before the question mark: =COUNTIF(range,"*~?*"). The tilde tells Excel to treat the ? as a literal character, not a wildcard. Similarly, for a literal asterisk, use "*~**".
Does COUNTIF wildcard work in Google Sheets?
Yes, Google Sheets supports the same wildcard characters (*, ?, and ~) in COUNTIF and COUNTIFS. The syntax is identical. However, Google Sheets is case-insensitive by default, just like Excel. One difference: in Google Sheets, you can use regex with REGEXMATCH inside SUMPRODUCT for more complex patterns.
Practical Tips for Using Wildcards Efficiently
To get the most out of wildcards in COUNTIF:
- Use
*at both ends for a “contains” match, but be aware this also matches cells that are only the word itself. - Use
?sparingly because it matches any character, including spaces and punctuation. For example,"b?g"matches “b g” as well as “bag”. - When you need to match a literal wildcard, always use the tilde. Forgetting it is a common cause of unexpected counts.
- Combine COUNTIF with other functions like IFERROR or LEN to handle edge cases. For instance, to count cells that have at least one character (non-empty), use
=COUNTIF(range,"?*")– the?matches the first character and*matches the rest. - If you’re building a dashboard, consider using a helper column with SEARCH or ISNUMBER to make complex logic more readable.
Wildcards in COUNTIF are a simple yet powerful way to count text patterns without needing complex formulas. Once you master the three characters and the tilde escape, you can handle most partial-match counting tasks directly.
Related
- Java String.join(): How It Works and When to Use It
- C++ String Count: How to Count Characters
- Oracle String to Value: Converting Text in SQL
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel MATCH Function: Arguments and Match TypesThe Excel MATCH function takes a value to find, a one-dimensional range to search, and an optional match…
- Databricks User-Defined Functions: How They Work in PracticeDatabricks user-defined functions (UDFs) let you apply custom logic to data in SQL or Spark when built-in functions…
- Excel XLOOKUP Examples for Common Spreadsheet TasksMicrosoft Excel’s XLOOKUP finds a value in one range and returns the corresponding value from another. For example,…
- How to Sort Names in Excel AlphabeticallyTo sort names alphabetically in Excel for Microsoft 365 on Windows, select the name list and any related…
- Excel Time Card Calculator: Formulas, Setup, and TipsYes. In Excel for Microsoft 365 on Windows, a basic time card calculator can subtract clock-in from clock-out,…
- How to Execute a Function in PostgreSQLExecuting a function in PostgreSQL means calling it so it runs its SQL logic and returns a result.…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.