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.
It comes up in this mock test: Excel assessment (Indeed format)
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.