To total rows whose label contains a word — every product with “Pro” in the name, all “West”-anything regions — use SUMIF with wildcards. The * stands for any characters.
The example
Total sales for any product whose name contains “Pro”.
| A | B | |
|---|---|---|
| 1 | Product | Sales |
| 2 | Widget Pro | $120 |
| 3 | Gadget | $90 |
| 4 | Pro Mouse | $60 |
| 5 | Cable | $40 |
| 6 | Total “Pro” sales: | $180 |
The formula
Total of the “Pro” products:
How it works
Wildcards make SUMIF match partial text:
- The criteria
"*Pro*"matches any label that has “Pro” somewhere inside — start, middle, or end. - SUMIF adds the matching rows’ values from
B2:B5: 120 + 60 =180. "Pro*"would match only labels that start with Pro;"*Pro"only those that end with it.- Use
?to match exactly one character, e.g."A?"matches A1, AX but not ABC.
Match a literal asterisk or question mark by prefixing it with a tilde: "~*" finds a real * character.
Try it: interactive demo
Type a fragment to match (wrapped in * automatically).
Variations
Contains text AND another condition
SUMIFS with a wildcard plus a second test:
Starts with / ends with
Anchor the wildcard:
Match a fragment from a cell
Concatenate the wildcards around a cell value:
Pitfalls & errors
No wildcards = exact match. =SUMIF(A2:A5, "Pro", B2:B5) totals only cells equal to exactly “Pro”. Add * for “contains.”
SUMIF is case-insensitive. “pro” and “PRO” both match. There is no case-sensitive SUMIF — use SUMPRODUCT with EXACT/FIND if you need that.
Cell-reference criteria need concatenation. You can’t put wildcards on a bare cell; build the string: "*"&E1&"*".
Practice workbook
Frequently asked questions
How do I sum cells that contain specific text in Excel?
How do I sum cells that start with or end with text?
How do I use a cell value as the contains criterion?
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