The COUNTIF function in Excel
COUNTIF counts the cells in a range that meet one condition.
What COUNTIF does
The condition can be text in quotes, a number, or a comparison such as ">100". The match ignores upper and lower case. Use it to answer questions like how many orders are shipped, or how many people work in one department.
Syntax
=COUNTIF(range, criteria)
- range
- The cells to look through.
- criteria
- The condition to match, such as "Shipped", 5 or ">100". Text and comparisons go in quotes.
Worked example
| A | B | |
|---|---|---|
| 1 | Order | Status |
| 2 | 1001 | Shipped |
| 3 | 1002 | Pending |
| 4 | 1003 | Shipped |
| 5 | 1004 | Shipped |
| 6 | 1005 | Pending |
| 7 | Shipped | 3 |
B7 =COUNTIF(B2:B6,"Shipped")
Three of the five rows say Shipped, so COUNTIF returns 3.
Now you try
A fresh exercise every time. Press Another exercise as often as you like.
Where you will use it
Sales & Ops
Count orders by status, such as Shipped or Late.
HR
Count how many people are in one department.
Data cleanup
Count how often an entry appears to find duplicates.
Common mistakes
- Leaving the quotes off a comparison. It must be ">100" in quotes, not >100.
- Stray spaces. "North " with a trailing space does not match "North".
- Trying to test two conditions at once. COUNTIF takes one. Use COUNTIFS for more.
The COUNTIF course
- 1Count what matchesFree practice belowPractise
- 2Count with two conditionsComing soon
- 3Count with wildcardsComing soon
- 4Count dates in a rangeComing soon
- 5Duplicate finderComing soon
- 6Dashboard countsComing soon
Common questions
Is COUNTIF case-sensitive?
No. "north" and "North" match each other.
Can COUNTIF use wildcards?
Yes. An asterisk matches any run of characters and a question mark matches a single character. For example "A*" counts entries that start with A.