COUNTIF
Counts how many cells match a condition: how many orders were delivered, how many employees are in a department, how many scores are below a threshold.
COUNTIF(range, criteria)- range
- Where to look, for example order statuses in B2:B8.
- criteria
- What to count: text, a number or a comparison in quotes.
Return value
The number of cells that match the condition. If there are none, the function returns 0.
Examples
How many orders are in each status
fx
A
B
C
D
E
F
1
2
3
4
5
6
7
8
9
10
Order
Status
Status
Orders
ORD-501
Delivered
Delivered
ORD-502
In transit
In transit
ORD-503
Delivered
Canceled
ORD-504
Canceled
ORD-505
Delivered
ORD-506
In transit
ORD-507
Delivered
Sheet1
Details
COUNTIF takes a range and a condition and returns the number of matching cells. Letter case doesn’t matter: “boston” and “Boston” count as the same.
The condition goes in quotes: "Delivered", ">=5000", "<>Canceled". In text you can use an asterisk: "*coffee*" finds every cell that contains the word “coffee”.
Common mistakes
- An extra space in the data: “Delivered ” with a trailing space won’t match the condition “Delivered”.
- A range without $ signs: when you drag the formula, it shifts down, and some rows stop being counted.
- A comparison without quotes: >=5000 without quotes breaks the formula, you need ">=5000".