Fixing Excel SUMIFS That Returns Incorrect Total When Criteria Use Wildcards
You've written a SUMIFS formula with a wildcard like "*East*", run it, and the total is clearly wrong — either too high, too low, or suspiciously zero. The formula itself looks fine. The problem is that wildcard matching in Excel has a handful of quiet rules that can silently break your results without any error message to guide you.
This article walks through every common root cause, gives you a concrete test for each one, and shows you the exact fix. No guesswork required.
What you'll learn
- How
SUMIFSactually interprets*and?wildcards in criteria - Why wildcards fail completely when your criteria range holds numbers
- How to spot and remove invisible spaces that break pattern matching
- How to escape literal asterisks and question marks in your data
- How to use
COUNTIFas a fast diagnostic tool before you trust anySUMIFStotal
Prerequisites
You need a basic familiarity with Excel formulas and a working spreadsheet you can experiment on. All examples here work in Excel 2016, 2019, 2021, and Microsoft 365. The behaviour described is consistent across Windows and Mac versions of Excel in those releases.
How SUMIFS wildcard matching actually works
Excel supports two wildcard characters in text criteria: * (matches any sequence of characters, including none) and ? (matches exactly one character). These work only in the criteria argument, not in the criteria range itself.
When you write SUMIFS(C2:C100, B2:B100, "*East*"), Excel walks through every cell in B2:B100, checks whether its text contains
Frequently Asked Questions
Why does SUMIFS with a wildcard return zero even when I can see matching text in the column?
The most common reason is that the criteria range contains numbers, not text — and wildcards only match text values in Excel. Use Text to Columns to convert the range to text, or switch to a SUMPRODUCT formula that converts numbers on the fly with the TEXT function.
How do I use a wildcard in SUMIFS when the search term comes from another cell?
Concatenate the wildcard characters as separate string literals around the cell reference: =SUMIFS(C2:C100, B2:B100, "*" & E2 & "*"). Never put the cell reference inside the quote marks, as that searches for the literal text of the reference name, not its value.
Can SUMIFS wildcards match a literal asterisk that appears in my data?
Yes, but you must escape it with a tilde immediately before it. Use ~* in your criteria string to match a literal asterisk, and ~? to match a literal question mark. If the criteria comes from a cell, pre-process it with SUBSTITUTE to insert the tildes programmatically.
Does SUMIFS wildcard matching work the same way across all Excel versions?
The wildcard behaviour — asterisk for any sequence, question mark for one character, tilde for escaping — has been consistent across Excel 2016, 2019, 2021, and Microsoft 365 on both Windows and Mac. The limitation of wildcards not matching numeric cells also applies to all these versions.
What is the difference between using * at the start versus both sides of a search term in SUMIFS?
Placing * on both sides — "*term*" — matches the term anywhere in the cell value. Placing * only at the end — "term*" — matches only cells where the value starts with that term. Placing * only at the start — "*term" — matches only cells where the value ends with it. Choose the pattern that reflects your actual matching intent.
📤 Share this article
Sign in to saveRelated Articles
Comments (0)
No comments yet. Be the first!