How to count cells that contain specific text in Excel
COUNTIF normally counts cells that match exactly. Wildcards let it match part of a cell instead.
An asterisk stands for any characters, so "*Cable*" matches any cell with Cable somewhere in it. It ignores capitals.
The formula
| A | |
|---|---|
| 1 | Product |
| 2 | USB Cable |
| 3 | Desk Lamp |
| 4 | HDMI Cable |
| 5 | Mouse |
=COUNTIF(A2:A5,"*Cable*")Result: 2
Two product names contain the word Cable.
Step by step
- Type =COUNTIF( and select the range.
- Add the text with an asterisk on each side, in quotes.
- To use a word from a cell, write "*"&D1&"*".
Other ways to write it
=SUM(--ISNUMBER(SEARCH("Cable",A2:A5)))SEARCH finds the word in each cell. Useful inside larger formulas.
Each of these gives the same result on the example above, in Excel and in Cellwise.
Common mistakes
- No wildcards
- COUNTIF(A2:A5,"Cable") only counts cells that are exactly Cable.
- Starts with, not contains
- "Cable*" only matches cells that start with Cable. Use an asterisk on both sides for contains.
Reading isn’t the same as doing
Partial matches and exclusions gives you real tasks on this, and tests your formulas on data you haven’t seen. It’s free, with no sign-up.