How to Use Wildcards in Excel Formulas

Introduction

You have a product list with names like "Laptop Pro 13" and "Laptop Air," and you need to count or sum all the Laptop entries without listing each one individually. Wildcard characters let you match partial text inside functions like COUNTIF, SUMIF, and VLOOKUP. Instead of writing a separate condition for each product name, a single pattern like "Laptop*" handles all of them at once.

The Dataset

Here's the product revenue table for all the examples below:

	A	B	C
3	Product	Category	Revenue
4	Laptop Pro 13	Electronics	4200
5	Laptop Pro 15	Electronics	5800
6	Laptop Air	Electronics	3600
7	Monitor 24in	Electronics	2100
8	Monitor 27in	Electronics	2900
9	Keyboard Wireless	Accessories	780
10	Mouse Pro	Accessories	320

The Wildcard Characters

Excel recognizes three wildcard characters in formula criteria:

Character	Matches	Example	Would Match
*	Any sequence of characters (including none)	"Laptop*"	Laptop Pro 13, Laptop Pro 15, Laptop Air
?	Exactly one character	"Laptop Pro ??"	Laptop Pro 13, Laptop Pro 15
~	Escapes a literal * or ?	"~?"	A literal question mark

The asterisk is the one you'll reach for most often. Where you place it controls what kind of match you get:

Pattern	Meaning
"Laptop*"	Starts with "Laptop"
"*Pro*"	Contains "Pro" anywhere
"*Wireless"	Ends with "Wireless"

This guide focuses on COUNTIF, SUMIF, and VLOOKUP, but these wildcards work the same way in AVERAGEIF, MAXIFS, MINIFS, and anywhere else Excel accepts text criteria.

Wildcards in COUNTIF

To count all Laptop products:

=COUNTIF(A4:A10, "Laptop*")

Product	Matches "Laptop*"?
Laptop Pro 13	Yes
Laptop Pro 15	Yes
Laptop Air	Yes
Monitor 24in	No
Monitor 27in	No
Keyboard Wireless	No
Mouse Pro	No

The result is 3, covering all three Laptop variants without naming each one.

To count products that contain "Pro" anywhere in the name:

=COUNTIF(A4:A10, "*Pro*")

This matches Laptop Pro 13, Laptop Pro 15, and Mouse Pro. The result is 3.

The ? wildcard is useful when the text you're matching has a fixed length. To count only the Laptop Pro models (which end in a two-digit number) while excluding Laptop Air:

=COUNTIF(A4:A10, "Laptop Pro ??")

Each ? matches exactly one character. "Laptop Pro 13" and "Laptop Pro 15" both fit the pattern. "Laptop Air" doesn't start with "Laptop Pro" so it's excluded. The result is 2.

Wildcards in SUMIF

Wildcards work the same way in SUMIF. Instead of counting matches, you're summing the corresponding values.

To sum revenue for all Laptop products:

=SUMIF(A4:A10, "Laptop*", C4:C10)

Product	Revenue	Matches?	Included
Laptop Pro 13	4200	Yes	4200
Laptop Pro 15	5800	Yes	5800
Laptop Air	3600	Yes	3600
Monitor 24in	2100	No	0
Monitor 27in	2900	No	0
Keyboard Wireless	780	No	0
Mouse Pro	320	No	0
		Total	13,600

To sum revenue for all products containing "Pro":

=SUMIF(A4:A10, "*Pro*", C4:C10)

The result is 10,320 (4200 + 5800 + 320), picking up both Laptop Pro models and Mouse Pro.

Multiple Conditions with Wildcards

SUMIFS and COUNTIFS accept wildcards in any of their criteria pairs. To sum revenue for products containing "Pro" that are in the Electronics category:

=SUMIFS(C4:C10, A4:A10, "*Pro*", B4:B10, "Electronics")

Product	Category	Revenue	Pro AND Electronics?
Laptop Pro 13	Electronics	4200	Yes
Laptop Pro 15	Electronics	5800	Yes
Laptop Air	Electronics	3600	No
Monitor 24in	Electronics	2100	No
Monitor 27in	Electronics	2900	No
Keyboard Wireless	Accessories	780	No
Mouse Pro	Accessories	320	No

The result is 10,000. Mouse Pro contains "Pro" but falls in Accessories, so it's excluded. Without the category condition, SUMIF would return 10,320.

You can also use wildcards in more than one criteria at once. To count Electronics products that start with "Laptop":

=COUNTIFS(A4:A10, "Laptop*", B4:B10, "Elec*")

The result is 3.

Wildcards in VLOOKUP

Wildcards aren't limited to counting and summing. VLOOKUP supports them in the lookup value when you use exact match mode (FALSE), so you can search by partial name instead of typing the full text.

To look up the revenue for the first product that starts with "Mouse":

=VLOOKUP("Mouse*", A4:C10, 3, FALSE)

VLOOKUP scans column A, finds "Mouse Pro" as the first match, and returns 320 from the third column.

XLOOKUP also supports wildcards, but you need to set the match_mode argument to 2:

=XLOOKUP("Mouse*", A4:A10, C4:C10, , 2)

The result is the same: 320. The empty fourth argument keeps the default "not found" behavior, and 2 tells XLOOKUP to treat the lookup value as a wildcard pattern.

Key takeaway: VLOOKUP supports wildcards by default in exact match mode. XLOOKUP requires match_mode set to 2. Both return the first match they find, so if multiple rows match, you'll get the one closest to the top of the range.

Using a Cell Reference as the Pattern

Hard-coding the wildcard text works, but building the pattern from a cell reference is more flexible. If cell E4 contains "Laptop," you can append the asterisk with concatenation:

=COUNTIF(A4:A10, E4 & "*")

This produces "Laptop*" at runtime, so changing E4 to "Monitor" automatically updates the count. The same approach works for any wildcard position:

Goal	Formula
Starts with E4	=COUNTIF(A4:A10, E4 & "*")
Contains E4	=COUNTIF(A4:A10, "*" & E4 & "*")
Ends with E4	=COUNTIF(A4:A10, "*" & E4)

This pattern applies to SUMIF, SUMIFS, VLOOKUP, and every other function that supports wildcards.

Escaping Wildcards with the Tilde

If your data contains a literal asterisk or question mark and you need to match it exactly, prefix it with a tilde (~).

Suppose a cell contains the text "Special Offer*" and you want to count only cells with that exact asterisk. A plain "*Offer*" would match anything containing "Offer," not just the one with the asterisk. Instead:

=COUNTIF(A4:A10, "*Offer~*")

The ~* tells Excel to treat the second asterisk as a literal character, not a wildcard. Without the tilde, both asterisks would act as wildcards and the formula would match far more than intended.

Pattern	Meaning
"~*"	Literal asterisk
"~?"	Literal question mark
"~~"	Literal tilde

Conclusion

Wildcards let you match partial text in COUNTIF, SUMIF, VLOOKUP, and the rest of the conditional function family. Once you're comfortable placing the asterisk at the start, end, or both sides of your text, most partial-matching problems come down to choosing the right pattern.

For conditional counting and summing with exact criteria, see our guides on COUNTIF and SUMIF and SUMIFS. For lookups, check out XLOOKUP or VLOOKUP vs. XLOOKUP. For locating text within a cell (where SEARCH also supports wildcards), see our guide on FIND and SEARCH.