The COUNTIF function in Excel
COUNTIF counts how many cells in a range match a condition. How many orders came from the North? How many amounts are above 100? How many names start with "A"?
For two or more conditions at the same time, use COUNTIFS.
Syntax
=COUNTIF(range, criteria)
| Argument | What it means |
|---|---|
| range | The cells to check. |
| criteria | The condition: a value ("North", 5), a comparison in quotes (">100"), a cell reference, or a wildcard pattern ("A*"). |
Examples
1. Count a text value
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Order | Region | Amount | Status | North orders | |
| 2 | 1001 | North | 250 | Paid | 3 | |
| 3 | 1002 | South | 90 | Paid | ||
| 4 | 1003 | North | 420 | Open | ||
| 5 | 1004 | East | 130 | Paid | ||
| 6 | 1005 | North | 75 | Paid |
Three cells in B2:B6 say North. Text matching ignores upper and lower case.
2. Count numbers above a limit
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Order | Region | Amount | Status | Orders over 100 | |
| 2 | 1001 | North | 250 | Paid | 3 | |
| 3 | 1002 | South | 90 | Paid | ||
| 4 | 1003 | North | 420 | Open | ||
| 5 | 1004 | East | 130 | Paid | ||
| 6 | 1005 | North | 75 | Paid |
250, 420 and 130 are above 100. Comparisons go inside the quotes.
3. Take the condition from a cell
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Order | Region | Amount | Status | Limit | 200 | |
| 2 | 1001 | North | 250 | Paid | Above limit | 2 | |
| 3 | 1002 | South | 90 | Paid | |||
| 4 | 1003 | North | 420 | Open | |||
| 5 | 1004 | East | 130 | Paid | |||
| 6 | 1005 | North | 75 | Paid |
Join the operator and the cell with &: ">"&G1 becomes ">200". Change G1 and the count updates (250 and 420).
4. Wildcards: text that ends with something
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Order | Region | Amount | Status | Region ends with "th" | |
| 2 | 1001 | North | 250 | Paid | 4 | |
| 3 | 1002 | South | 90 | Paid | ||
| 4 | 1003 | North | 420 | Open | ||
| 5 | 1004 | East | 130 | Paid | ||
| 6 | 1005 | North | 75 | Paid |
* stands for any number of characters, ? for exactly one. North (3 times) and South end with "th": 4.
Common mistakes
The operator outside the quotes, e.g.
=COUNTIF(C2:C6,>100). Excel won't accept the formula.Fix: Write the whole condition as text: ">100".
Putting the cell reference inside the quotes, e.g. ">G1". Excel then looks for the text G1.
Fix: Join them with &: ">"&G1.
Counting the wrong thing: COUNTIF counts cells, it doesn't add them up.
Fix: To add the amounts that match, use SUMIF.
Frequently asked questions
How do I count cells that are not empty?
Use "<>" as the condition: =COUNTIF(B2:B6,"<>"), or simply COUNTA.
Can COUNTIF use two conditions?
No, one range and one condition only. COUNTIFS takes as many range/condition pairs as you need.
Related functions
- COUNTIFSCOUNTIFS counts the rows that meet several conditions at once, such as a region and an amount range.
- SUMIFSUMIF adds up the values that meet one condition, such as all sales of one region or all amounts above 100.
- IFIF checks a condition and returns one value if it's true and another if it's false.