Count with a wildcard
PremiumIn D1, count the products in A2:A8 with the word Chair anywhere in the name. * stands for any text, so the criterion is "*Chair*".
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Product | Chairs | ||||
| 2 | Office Chair Pro | |||||
| 3 | Desk Lamp | |||||
| 4 | Kids Chair | |||||
| 5 | Standing Desk | |||||
| 6 | Chair Cushion | |||||
| 7 | Monitor Arm | |||||
| 8 | Gaming Chair | |||||
| 9 |
Premium lesson
Unlock COUNTIF 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:SUMIFSUMIFS
Need help?
Why this works
In a COUNTIF criterion, * matches any number of characters, including none, so "*Chair*" matches Office Chair Pro, Kids Chair, Chair Cushion and Gaming Chair: 4. The match ignores upper and lower case. A ? matches exactly one character.
Common mistake: Leaving out the wildcards. "Chair" only matches a cell that says Chair and nothing else.
The answer
- D1
=COUNTIF(1A2:A8,2"*Chair*")
In plain words: Count the product names 1in A2:A8 that have 2Chair anywhere in them.