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