What COUNTIF Does
COUNTIF counts the number of cells in a range that meet a specific condition. You can count how many times a value appears, how many cells are above a threshold, or how many entries match a text pattern. It is one of the go-to functions for quick data summaries.
Syntax
=COUNTIF(range, criterion)
| Parameter | Description |
|---|---|
| range | The range of cells to evaluate. |
| criterion | The condition to match. Can be a number, text, expression (e.g., ">50"), or cell reference. |
Basic Examples
Example 1: Count Occurrences of a Value
How many orders came from the "West" region?
| A | |
|---|---|
| 1 | Region |
| 2 | East |
| 3 | West |
| 4 | West |
| 5 | East |
| 6 | West |
=COUNTIF(A2:A6, "West")
Result: 3
Example 2: Count Values Above a Threshold
How many students scored above 80?
=COUNTIF(B2:B30, ">80")
Result: Returns the count of all scores greater than 80.
Example 3: Count Non-Empty Text Cells Matching a Pattern
Count all product names that start with "Pro":
=COUNTIF(A2:A100, "Pro*")
The * wildcard matches any characters after "Pro".
Advanced Examples
Count Unique Values
Combine COUNTIF with SUMPRODUCT to count how many distinct values exist in a range:
=SUMPRODUCT(1/COUNTIF(A2:A50, A2:A50))
This divides 1 by the count of each value (so duplicates contribute fractionally) and sums the result. If "East" appears 3 times, each contributes 1/3, totaling 1.
Count Cells Within a Date Range
Count orders placed in March 2026:
=COUNTIF(C2:C500, ">="&DATE(2026,3,1)) - COUNTIF(C2:C500, ">="&DATE(2026,4,1))
This counts everything from March 1 onward, then subtracts everything from April 1 onward, leaving only March dates.
Common Mistakes
- Forgetting quotes around comparison operators.
=COUNTIF(B2:B10, >50)is invalid. It must be=COUNTIF(B2:B10, ">50")with quotes. - Case sensitivity. COUNTIF is not case-sensitive. "apple", "Apple", and "APPLE" all count as the same value. If you need case-sensitive counting, use SUMPRODUCT with EXACT.
- Using COUNTIF for multiple criteria. COUNTIF only accepts a single criterion. If you need to count based on two or more conditions (e.g., region = "West" AND status = "Active"), use COUNTIFS.
SheetAI Tip
Don't want to memorize this syntax? With SheetAI, just type what you need in plain English — like "count how many orders are from the West region" — and the formula is generated for you. Install SheetAI to try it free.