Excel Wildcards: *, ? and ~ in COUNTIF and SUMIF

An Excel wildcard is a symbol that stands for unknown characters, so your criteria can match part of the text instead of all of it. Use * for "any characters", ? for "exactly one character", and ~ when you need a real asterisk or question mark. This guide shows each one in COUNTIF, SUMIF and COUNTIFS on a small inventory table, and explains when wildcards won't work.

What are the wildcard characters in Excel?#

Excel has three wildcard characters, and they work inside the criteria of functions like COUNTIF and SUMIF:

Wildcards are not case-sensitive, so "cab-*" matches CAB-101 too. They always go inside the double quotes.

The examples use this inventory table in A1:D9:

How to use the * wildcard: starts with, ends with, contains#

Where you put the asterisk decides what you match. After the text means "starts with", before it means "ends with", and on both sides means "contains".

The Charger total is 132 + 18 + 55. Without the asterisks, "Charger" would only match a cell that says exactly Charger, and the result would be 0. The last row shows that <> works with wildcards too: it counts everything that does not match the pattern.

How to use the ? wildcard for one character#

A question mark stands for exactly one character, which is useful for codes with a fixed length. To count the mouse SKUs, which are MSE-30 plus one more character:

=COUNTIF(A2:A9,"MSE-30?")

The result is 2 (MSE-301 and MSE-302). "CHG-20?" gives 3. You can repeat it: "???-1*" means three characters, a dash, a 1, then anything, so it matches the three CAB-10x codes.

Quick reference: common wildcard patterns#

The first two count SKUs in column A; the others count products in column B. "*" only counts text, so it's a quick way to spot numbers hiding in a text column: compare it with COUNTA.

How to match a real * or ? with the tilde (~)#

Put a tilde before an asterisk or question mark when you mean the character itself. Say some product names end with an asterisk to mark discontinued items, like Car Charger*. To count them:

=COUNTIF(B2:B9,"*~*")

The first * is a wildcard (any characters) and ~* is a real asterisk. Without the tilde, "**" would match every text cell. In the same way, "*~?*" counts cells that contain a question mark, and "*~~*" counts cells that contain a tilde.

How to combine wildcards with a cell reference#

Join the wildcard to a cell with &, so you can change the search word without editing the formula. If H1 holds the word Mouse:

=SUMIF(B2:B9,"*"&H1&"*",D2:D9)

This adds the stock of every product containing Mouse: 47 + 12 = 59. Type Cable in H1 and the result changes to 389. Keep the * inside quotes and H1 outside them. Writing "*H1*" would look for the letters H1.

Wildcards with COUNTIFS and SUMIFS#

Wildcards work in any criteria pair of COUNTIFS and SUMIFS. To count charger SKUs in the Leeds warehouse:

=COUNTIFS(A2:A9,"CHG-*",C2:C9,"Leeds")

The result is 2 (CHG-201 and CHG-203). For more on combining conditions, see COUNTIFS and SUMIFS with multiple criteria. If you want the basics of each function first, read how to use COUNTIF and how to use SUMIF.

Wildcards in AVERAGEIF, MAXIFS and MINIFS#

The other conditional functions read wildcards in exactly the same way. The average stock of the cable SKUs:

=AVERAGEIF(A2:A9,"CAB-*",D2:D9)

That's 389 ÷ 3 = 129.67. And the largest stock among chargers, in Excel 2019 or newer:

=MAXIFS(D2:D9,B2:B9,"*Charger*")

The result is 132 (the Wall Charger). One common slip: a space inside the pattern counts as a character. "* Cable*" needs a space before Cable, so it would miss a product that starts with the word Cable.

When don't wildcards work?#

Wildcards only match text, and only in functions built to read them. Watch for these cases:

• Real numbers. A number like 101 is not text, so "1*" won't match it. Product codes typed as numbers need to be text for wildcards to work.
• Dates. Dates are numbers too, so "2025*" won't find dates in 2025. Use a date range instead: sum by date range or month with SUMIFS.
• Comparisons. =A2="CAB*" checks for the exact text CAB*, so it returns FALSE for CAB-101. In IF, use ISNUMBER(SEARCH("CAB",A2)) to test for contains.
• FIND. FIND treats * and ? as normal characters. SEARCH supports wildcards.

Lookups can use wildcards too: VLOOKUP and MATCH with an exact match, and XLOOKUP with match_mode 2. See wildcard matches with XLOOKUP.

For syntax references, see the COUNTIF guide and the SUMIF guide.

Practise it#

Practise wildcards and cell-based criteria in a live spreadsheet. You'll count SKUs that start with CAB-, total stock for products containing a word, use ? for one character, and build criteria like ">"&H5 and "*"&H8&"*".