Total only the rows whose label contains a word — all “North” regions, every product with “Pro” in the name. Wildcards in SUMIF make the criteria a pattern.
* wildcards match any characters before and after “north,” so partial matches add up.
The example
All “North” regions summed.
| A | B | |
|---|---|---|
| 1 | Region | Sales |
| 2 | North-East | 100 |
| 3 | North-West | 150 |
| 4 | *north* total | 250 |
The formula
Sum on a contains pattern:
How it works
Wildcards turn the criteria into a pattern:
*matches any run of characters;?matches a single one."*north*"means “contains north”;"north*"means “starts with.”- SUMIF adds the amounts wherever the label matches the pattern (case-insensitive).
- Reference a cell for the term:
"*"&E1&"*"makes it interactive.
SUMIFS too: wildcards work in SUMIFS criteria for multi-condition sums. To match a literal * or ?, escape it with a tilde (~*).
Try it: interactive demo
Lines “label,amount”; sum those containing the term.
Variations
Starts with
Prefix only:
From a cell
Interactive:
SUMIFS
With another condition:
Pitfalls & errors
Exact-match only by default. Without wildcards, SUMIF matches the whole label; add * for “contains.”
Literal * or ?. Escape with a tilde (~*) to match the actual character.
Numbers aren’t text. Wildcards work on text labels, not numeric criteria.
Practice workbook
Frequently asked questions
How do I use SUMIF with a partial match in Excel?
How do I sum labels that start with a term?
Do wildcards work in SUMIFS?
Stop fighting formulas. Learn them in a day.
This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class