COUNTIF Function in Google Sheets
Counts the cells in a range that meet a single condition. The most-used way to answer "how many of X are there?" in a spreadsheet.
Syntax
COUNTIF(range, criterion)Returns: The number of cells in range that match criterion.
Excel equivalent: COUNTIF (identical)
Parameters
| Name | Required | Description |
|---|---|---|
| range | Required | The range of cells to test. |
| criterion | Required | The condition to match. A value (10), text ("Apple"), or comparison (">100"). |
Examples
Count orders for one customer
=COUNTIF(A:A, "Acme Corp")Returns how many times "Acme Corp" appears in column A.
Count values above a threshold
=COUNTIF(B:B, ">100")Counts cells in column B with values greater than 100. Comparisons must be in quotes.
Check if a value exists at all
=COUNTIF(A:A, "alice@example.com") > 0Returns TRUE if the email appears anywhere in column A, FALSE otherwise. A common pattern for duplicate detection.
When to use an alternative
- COUNTIFS — You have two or more conditions.
- COUNTA — You just want to count non-empty cells (no condition).
- MATCH wrapped in ISNUMBER — You only need to know IF a value exists, not how many — slightly faster than COUNTIF for that use case.
Common errors and how to fix them
Counts 0 unexpectedly
Cause: Trailing whitespace or text/number type mismatch in the data.
Fix: Use TRIM() to clean cells, or check whether numbers are stored as text (left-aligned in the cell).
Wildcards not matching
Cause: criterion missing the * or ? wildcard characters.
Fix: Use "*Pro*" to match any cell containing "Pro". For an exact "Pro" match, omit wildcards.
Related functions
Frequently Asked Questions
What's the difference between COUNT, COUNTA, and COUNTIF?
COUNT only counts cells with numbers. COUNTA counts cells with anything (text, numbers, even error values) — everything except truly empty cells. COUNTIF counts cells matching a specific condition. Use COUNTA for "how many filled cells", COUNT for "how many numeric cells", COUNTIF for "how many cells matching X".
Are COUNTIF comparisons case-sensitive?
No, COUNTIF is case-insensitive by default — "apple" and "Apple" count as the same value. For case-sensitive counting, use SUMPRODUCT with EXACT: SUMPRODUCT(--EXACT(A:A, "Apple")).