Excel COUNTIF Wildcard: Use * and ? in Text Matches

Formulas & functions · Updated November 22, 2026

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

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.