SUMIF with a wildcard
PremiumIn E1, total the sales of every product with Chair anywhere in its name. Use * on both sides of the word: SUMIF(A2:A8,"*Chair*",B2:B8).
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Product | Sales | Chair sales | |||
| 2 | Office Chair Pro | 1450 | ||||
| 3 | Desk Lamp | 320 | ||||
| 4 | Kids Chair | 480 | ||||
| 5 | Standing Desk | 2100 | ||||
| 6 | Chair Cushion | 150 | ||||
| 7 | Monitor Arm | 640 | ||||
| 8 | Gaming Chair | 1890 | ||||
| 9 |
Premium lesson
Unlock SUMIF and every other lesson
This is a premium lesson. Premium lets you solve it and every other lesson, take the timed mock tests, and earn certificates you can share on LinkedIn.
Next with premium:SUMIFSCOUNTIFS
Need help?
Why this works
"*Chair*" matches any name with Chair in it, whatever comes before or after: Office Chair Pro, Kids Chair, Chair Cushion and Gaming Chair. Their sales add up to 1,450 + 480 + 150 + 1,890 = 3,970.
Common mistake: Forgetting the third argument. The criterion is about the names, but the numbers to add are in column B.
The answer
- E1
=SUMIF(1A2:A8,2"*Chair*",3B2:B8)
In plain words: Add up 3the sales in B2:B8 on the rows where 1the product in A2:A8 has 2Chair anywhere in its name.