To count how many cells contain a word or fragment — every product with “Pro” in the name, every address that mentions “TX” — use COUNTIF with wildcards. The * matches any characters around your text.
The example
Count the products whose name contains “Pro.”
| A | B | |
|---|---|---|
| 1 | Product | |
| 2 | Widget Pro | |
| 3 | Gadget | |
| 4 | Pro Mouse | |
| 5 | Cable | |
| 6 | Count with “Pro”: | 2 |
The formula
The count of “Pro” products:
How it works
Wildcards turn COUNTIF into a “contains” counter:
- The criteria
"*Pro*"matches any cell with “Pro” somewhere inside. - COUNTIF tallies the matching cells —
2. "Pro*"counts only cells that start with Pro;"*Pro"only those that end with it.- Use
?for a single character:"A?"matches A1 and AX but not ABC.
Count cells that do NOT contain text with the <> operator: =COUNTIF(A2:A8, "<>*Pro*") — though blanks behave oddly, so combine with COUNTA if needed.
Try it: interactive demo
Type a fragment to count (wrapped in * automatically).
Variations
Contains AND another condition
COUNTIFS with a wildcard plus a second test:
Count that do NOT contain
Use the not-equal operator:
Use a cell value as the fragment
Concatenate wildcards around a cell:
Pitfalls & errors
No wildcards = exact match. =COUNTIF(A2:A8, "Pro") counts only cells equal to exactly “Pro.” Add * for “contains.”
COUNTIF is case-insensitive. “pro” and “PRO” both match. Use SUMPRODUCT with FIND for a case-sensitive count.
Numbers stored as text may not match a numeric-looking wildcard the way you expect. Keep the data type consistent.
Practice workbook
Frequently asked questions
How do I count cells that contain specific text in Excel?
How do I count cells that start with or end with text?
How do I count cells that do not contain a word?
Stop fighting formulas. Learn them in a day.
This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class