Wildcards let you match text that is only partly known. Excel has three of them.
The three wildcards
- * (asterisk): any number of characters, including none.
- ? (question mark): exactly one character.
- ~ (tilde): tells Excel to treat the next * or ? as a normal character.
Examples with COUNTIF
- Starts with "Pro":
=COUNTIF(A2:A100, "Pro*")
- Ends with "ltd":
=COUNTIF(A2:A100, "*ltd")
- Contains "tax":
=COUNTIF(A2:A100, "*tax*")
- Exactly 5 characters:
=COUNTIF(A2:A100, "?????")
SUMIFS and VLOOKUP
=SUMIFS(C2:C100, A2:A100, "*Laptop*") adds up every row whose product name contains Laptop.
=VLOOKUP("Priya*", A2:C100, 3, FALSE) finds the first name starting with Priya. In XLOOKUP, set the match mode to 2: =XLOOKUP("Priya*", A2:A100, C2:C100, "None", 2).
Using a cell reference
Join the wildcard to the cell: =COUNTIF(A2:A100, "*"&F1&"*").
Search for a real * or ?
=COUNTIF(A2:A100, "~*") counts cells that contain an actual asterisk.
Wildcards work with text only. They do not work on numbers.
Worked example
=COUNTIF(A2:A100,"Pu*") counts cells starting with Pu. =COUNTIF(A2:A100,"*pen*") counts cells containing pen. =COUNTIF(A2:A100,"A?C") matches A, any one character, then C. To find a real star, use ~*.
Common mistakes
- Using wildcards with numbers.
- Using * where you meant ?.
- Forgetting wildcards need quotes.
Frequently asked questions
Which wildcards exist?
* for any number of characters, ? for one character, ~ to treat * or ? as normal.
Which functions accept them?
COUNTIF(S), SUMIF(S), AVERAGEIF(S), MATCH, VLOOKUP, XLOOKUP (match mode 2).
Does IF accept wildcards?
No. Use SEARCH or COUNTIF inside IF.
Practice this online, free
Reading is only half of learning Excel. Cellgrids is a free place for online Excel practice: solve Excel formula exercises in a live spreadsheet, prepare for Excel interview questions, and work through business case studies built for data analytics and business analytics roles.
Practice these formulas: COUNTIF, SUMIF, VLOOKUP, XLOOKUP.
Start Excel practice → Case studies
