Excel guide · 2 min read
How to sum if a cell contains text in Excel
=SUMIF(A2:A8,"*blue*",B2:B8). The * matches any characters, so it adds every row whose item contains "blue" anywhere, ignoring case.Sample data
Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Item | Amount | Region | Search | hoodie | |
| 2 | T-shirt Blue M | 18 | East | |||
| 3 | Hoodie Grey L | 42 | West | |||
| 4 | Navy blue cap | 15 | East | |||
| 5 | Blue jeans 32 | 55 | West | |||
| 6 | Socks Black | 9 | East | |||
| 7 | Hoodie Blue S | 42 | East | |||
| 8 | Bluetooth speaker | 60 | West |
SUMIF with wildcards
SUMIF(range, criteria, sum_range) checks each cell in range and adds the matching cells from sum_range. Put * on both sides of the word to mean "contains". It is not case-sensitive, so "blue" finds "Blue" and "BLUE" too.
Watch out for partial words: "*blue*" also matches "Bluetooth speaker". Here that adds 60 you probably did not want.
Result: 190
Returns 190, which includes the 60 from the Bluetooth speaker.
Tighten the match
To catch "blue" as its own word, add spaces inside the wildcards, or match where it sits. "Blue*" means "starts with Blue" and "* Blue *" means " Blue " appears with spaces around it. Choose the pattern that fits how your names are written. For this list, summing "*blue*" and subtracting the "*bluetooth*" rows is the simplest fix.
Result: 130
Returns 130, the blue clothing only.
Use a search cell instead of typing the text
Join the wildcards to a cell with &. Then anyone can type a word in F1 and the total updates, which makes a handy little search box.
Result: 84
With "hoodie" in F1, this returns 84.
Add more conditions with SUMIFS
SUMIFS takes the sum range first, then any number of range and criteria pairs. All of them must match. Here: items containing "blue" sold in the East.
Result: 75
Returns 75: the blue T-shirt, navy blue cap and blue hoodie.
Sum if a cell does not contain text
Put <> in front of the pattern to reverse it. "<>*blue*" adds every row without "blue" in the name.
Result: 51
Returns 51, the grey hoodie and black socks.
Common mistakes
Wildcards on numbers
Wildcards only match text. "*5*" will not find the number 150. Convert to text with a helper column or use a numeric condition like ">100".
Sum range and criteria range are different sizes
Keep both ranges the same height, like A2:A8 and B2:B8, or the sum lines up with the wrong rows.
Searching for a real asterisk or question mark
Put a tilde in front: "*~**" finds cells containing *. The tilde tells Excel to treat the next character literally.
FAQ
How do I sum cells that contain specific text in Excel?
Use =SUMIF(text_range,"*word*",sum_range). The asterisks mean any characters before and after the word.
Is SUMIF case-sensitive?
No. SUMIF and SUMIFS ignore case. For a case-sensitive match you need SUMPRODUCT with EXACT or FIND instead.
How do I sum if a cell contains one of several words?
Add separate SUMIFs together, such as =SUMIF(A:A,"*blue*",B:B)+SUMIF(A:A,"*navy*",B:B). Make sure no row matches both, or it is counted twice.
How do I sum if a cell starts with certain text?
Put the asterisk only at the end: =SUMIF(A2:A8,"Hoodie*",B2:B8). Use "*Blue" for cells that end with Blue.
Why does my SUMIF with wildcards return 0?
Check for typos, extra spaces in the criteria, and that the sum range really holds numbers. Also make sure the criteria is in quotes and the asterisks are inside them: "*blue*", not *"blue"*.
Functions used
Related guides
- How to use COUNTIF greater than in Excel
- How to count cells with text in Excel
- Excel IF statement with multiple conditions
- SUMIFS with multiple criteria in Excel
- Excel formulas cheat sheet: the 25 everyone should know
Updated