SUMIFS Wildcard Not Working? Common Fixes

If SUMIFS wildcards are not working, check that the wildcard is inside a quoted text criterion and that the criteria range contains text values to match. In Excel for Microsoft 365, an asterisk (*) matches any number of characters, a question mark (?) matches one character, and a tilde (~) makes the next wildcard character literal.

What SUMIFS wildcards do

SUMIFS adds values only when all of its criteria are satisfied. Its first argument is the range to add; the remaining arguments are pairs of a criteria range and a criterion. A wildcard belongs in the criterion, not in the sum range or in the criteria-range reference. Wildcards are useful when entries vary, but share a word, prefix, suffix, or pattern.

* stands for zero or more characters. A criterion such as North* can match North, Northeast, or North-West.
? stands for exactly one character. A criterion such as A?B can match A1B or A-B, but not AB.
~ escapes the next wildcard character. Use it when the data contains an actual asterisk or question mark.

Wildcard matching is not case-sensitive in SUMIFS. A criterion for North can therefore match NORTH as well. Wildcards are for matching text; they do not turn a number stored as a number into text or search the number’s displayed formatting.

Check the SUMIFS structure first

Before changing the wildcard, verify the formula’s shape. The sum range comes first, followed by a criteria range and criterion, then any additional range-and-criterion pairs. Each criteria range needs to cover the same number of rows and columns as the sum range. A mismatched range can cause an error or an unintended result.

For example, with categories in A2:A7 and amounts in B2:B7, the following criterion sums rows whose category contains North. The categories are North, Northeast, South, North-West, West North, and NORTH; the corresponding amounts are 120, 80, 90, 50, 30, and 40. The result is 320: the five entries containing North contribute 120 + 80 + 50 + 30 + 40.

With those categories in A2:A7 and amounts in B2:B7, this formula returns 320:

=SUMIFS(B2:B7,A2:A7,"*North*")

The quotes matter: they make the wildcard pattern a text criterion. In a formula, *North* without quotation marks is not a valid text criterion. Also check that the formula has not accidentally put the wildcard in the sum-range argument.

Choose the right wildcard pattern

The position of the asterisk changes what qualifies. If the same category and amount data is in A2:A7 and B2:B7, this formula returns 290. It matches entries that begin with North: North (120), Northeast (80), North-West (50), and NORTH (40). It does not include West North (30).

With categories in A2:A7 and amounts in B2:B7, the starts-with example is:

=SUMIFS(B2:B7,A2:A7,"North*")

Common patterns include:

"*term*" matches text containing term anywhere.
"term*" matches text beginning with term.
"*term" matches text ending with term.
"A?B" matches A, then one character, then B.

Leaving out the asterisks asks for a whole-cell text match rather than a contains match. For the same sample data, a criterion of North matches North and NORTH, because matching ignores case, but not Northeast or North-West. The total is 160.

Build a wildcard criterion from a cell

A common reason a formula fails is that the user types a search term in a cell but forgets to join it to the wildcard characters. If D2 contains North, the criterion can be built by joining an asterisk before and after the cell reference. With categories in A2:A7, amounts in B2:B7, and North in D2, the formula returns 320:

=SUMIFS(B2:B7,A2:A7,"*"&D2&"*")

The ampersands join text pieces together. The quotation marks enclose the literal asterisks, while D2 refers to the cell holding the search term. If D2 is blank, this pattern becomes two asterisks; that may match far more rows than intended. Check that the search cell is populated and contains the value you expect.

When a criterion combines a wildcard with another condition, keep each criterion range and condition as its own pair. For instance, suppose A2:A5 contains North Store, South, North Outlet, and North Store; B2:B5 contains actual Excel dates of January 5, January 15, January 20, and February 2, 2026; and C2:C5 contains 100, 200, 75, and 50. The formula below finds entries containing North dated in January. The result is 175, from 100 + 75.

With those category, date, and amount values in the stated ranges, use:

=SUMIFS(C2:C5,A2:A5,"*North*",B2:B5,">="&DATE(2026,1,1),B2:B5,"<"&DATE(2026,2,1))

This example also shows why dates should be real Excel dates, not text that merely looks like a date. The less-than-first-of-next-month boundary includes every time on January 31 if the date cells contain times as well as dates. Excel for Mac uses the same SUMIFS syntax; list separators can differ with regional settings, so some installations require semicolons instead of commas.

Common reasons a wildcard appears not to work

The criterion is missing quotes or concatenation. A typed pattern needs quotation marks; a pattern assembled from a cell needs quoted wildcard characters joined to the cell reference.
The source values are not text in the expected form. A wildcard will not match a number merely because a custom number format displays extra characters. Check the underlying cell value and whether an identifier was imported as a number or as text.
There are extra spaces or hidden characters. A leading space changes the beginning of a string, and a trailing space can prevent an apparent exact match. Imported data may also contain nonbreaking spaces or other characters that are hard to see. Inspect the source cells and clean the data when appropriate.
The formula uses the wrong pattern. North* means starts with North, while *North* means contains North. A missing or misplaced asterisk changes the matching rows.
The ranges do not line up. If the sum range starts on row 2, the criteria range should normally start on row 2 and cover the corresponding records. Check for an off-by-one reference or a range that stops early.
The actual wildcard character is in the data. An unescaped asterisk or question mark in a criterion acts as a wildcard, not as a literal symbol. Escape it with a tilde.
The data changed, but the formula points elsewhere. Confirm that the criteria range includes new rows and that the formula is not referring to a similarly named column or another sheet.

To narrow down a problem, inspect a few cells that should match and compare their exact contents with the intended pattern. Temporarily try a single, known category and then add other criteria back one at a time. This helps distinguish a wildcard issue from a date condition, a misaligned range, or an unexpected source value.

Match a literal asterisk or question mark

Because * and ? have special meanings, use ~ before either symbol when you need to match the character itself. With item names in A2:A5—A*B, A1B, AB, and A?B—and amounts in B2:B5—10, 20, 30, and 40—the first formula matches only the literal text A*B and returns 10.

With those item names in A2:A5 and amounts in B2:B5, match a literal asterisk with:

=SUMIFS(B2:B5,A2:A5,"A~*B")

For the same ranges, this criterion matches the literal question mark in A?B and returns 40:

=SUMIFS(B2:B5,A2:A5,"A~?B")

To match a literal tilde, double it in the criterion as ~~. Escaping is especially relevant for product codes, filenames, or labels copied from systems that use these symbols as ordinary characters.

How to check the result in Excel

Start with the smallest useful test: confirm that the criterion range contains the text you expect, then check whether the wildcard pattern describes it. Review the formula in the formula bar and verify the range references, quotes, ampersands, and criteria order. If the workbook has other filters or conditions in the same SUMIFS, temporarily remove those conditions from a copy of the formula and add them back individually.

It can also help to filter the source column for the expected text and compare the visible matching rows with the rows the formula should total. Be careful not to treat a filtered view as proof that SUMIFS is using the filter: SUMIFS evaluates its referenced data ranges and criteria, rather than summing only the rows currently visible. When working with imported records, check a suspected match directly in the cell, not just its displayed appearance.

Frequently Asked Questions

Can SUMIFS use wildcards?
Yes. In Excel, SUMIFS criteria can use * for any number of characters and ? for one character. Put the pattern in the criterion, in quotation marks, or build it from a cell reference using concatenation.

Why does SUMIFS with * return zero?
Check that the asterisk is part of a quoted text criterion, the criteria range is correct, and the matching entries are text in the form your pattern expects. Also look for hidden spaces, a missing wildcard, or another condition that excludes the rows.

How do I make SUMIFS match text that contains a word?
Place an asterisk on each side of the word in the criterion. The pattern "*word*" means the text can have any characters before and after the word. If the word is stored in a cell, join the two wildcard characters to that reference.

Is SUMIFS wildcard matching case-sensitive?
No. SUMIFS wildcard matching is not case-sensitive, so a criterion for North can match north or NORTH. If case-sensitive matching is essential, SUMIFS alone is not the right tool; a different formula approach is needed.

How do I match an actual asterisk in Excel?
Put a tilde immediately before the asterisk in the criterion: ~*. Use ~? to match a literal question mark and ~~ to match a literal tilde.

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

More in Errors & fixes

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

Spotted an error in this guide? Let us know.