Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to count how many times a value appears in Excel and Google Sheets
Excel & Google Sheets
=COUNTIF(A2:A6,"Apple")✓
Verified example
Computed by a real spreadsheet engine on the sample data below.
| Fruit |
| Apple |
| Banana |
| Apple |
| Cherry |
| Banana |
=COUNTIF(A2:A6,"Apple")→2
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
- 1Select the output cell.
- 2Type =COUNTIF( and select the range to search, e.g. A2:A6.
- 3Enter the value to count as the second argument — wrap text in quotes: "Apple".
- 4Close the parenthesis and press Enter.
- 5To count a value stored in another cell (e.g. D1), use =COUNTIF(A2:A6,D1) without quotes.
Tips
- COUNTIF is case-insensitive: 'apple' and 'Apple' are treated the same.
- Use wildcards: "App*" counts anything starting with 'App'; "*le" counts anything ending with 'le'.
- For numbers, omit quotes: =COUNTIF(A2:A6,42).
- To count non-empty cells use "<>" as the criterion.
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
How do I count case-sensitively?
COUNTIF is always case-insensitive. For case-sensitive counting use =SUMPRODUCT((EXACT(A2:A6,"Apple")*1)) which returns an exact match count.
Can I count multiple different values at once?
Use separate COUNTIF calls and add them: =COUNTIF(A2:A6,"Apple")+COUNTIF(A2:A6,"Banana"). For a single criterion with multiple values, COUNTIFS or SUMPRODUCT are better.