Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to use COUNTIF with a wildcard in Excel and Google Sheets
Topic:COUNTIF & COUNTIFSExcel & Google Sheets
=COUNTIF(A2:A5,"*apple*")✓
Verified example
Computed by a real spreadsheet engine on the sample data below.
| Product |
| Green Apple |
| Banana |
| Pineapple |
| Apple Juice |
=COUNTIF(A2:A5,"*apple*")→3
Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
Step by step
- 1Click the cell where you want the count.
- 2Type =COUNTIF(A2:A100, then enter your wildcard pattern in quotes.
- 3Use * to match zero or more characters: "apple*" matches anything starting with apple; "*apple" matches anything ending with it; "*apple*" matches anywhere.
- 4Use ? to match exactly one unknown character, e.g., "app?e" matches apple or appie.
- 5Press Enter to see the count.
Tips
- Combine a cell reference with wildcards using & — for example =COUNTIF(A2:A100,"*"&D1&"*") counts cells containing whatever is in D1.
Working on a sheet you inherited? Run the Auditor on the whole file first — it flags every #REF!, #N/A, broken column pattern, and inconsistent formula in seconds, free, no signup.
Frequently asked
What if I want to match a literal asterisk?
Prefix it with a tilde: "~*" matches a real asterisk character rather than acting as a wildcard.
Can I use wildcards with COUNTIFS?
Yes, COUNTIFS supports the same * and ? wildcards in any of its criteria arguments.
More on COUNTIF & COUNTIFS
See all →Reference
COUNTIF
Count the cells in a range that meet a condition.
How-toCOUNTIF vs COUNTIFS: how they differ in Excel and Sheets
Learn the difference between COUNTIF (single condition) and COUNTIFS (multiple conditions) for counting in Excel and Google Sheets.
Error fixFix COUNTIF
COUNTIF not working in Excel and Google Sheets — fix criteria syntax, data-type mismatches, wildcard errors, and case-sensitivity misunderstandings.
ReferenceCOUNTIFS
Count cells that meet several conditions simultaneously.
How-toHow to count cells not equal to a value
Use COUNTIF with the <> operator to count all cells that do not match a specific value.
Error fixFix Wrong count
COUNTIF returns the wrong count in Excel and Google Sheets — fix wildcard over-matching, duplicate rows, hidden rows, blank cell handling, and date format issues.