Function guides · Statistical
COUNTIF function
Counts the cells in a range that meet one condition.
Syntax
=COUNTIF(range, criteria)
| Argument | What it is |
|---|---|
range | The cells to check and count. |
criteria | The condition, such as "West", ">100", or ">"&E1. |
What COUNTIF does
COUNTIF counts the cells in a range that meet a single condition. It is the entry point to conditional formulas in Excel and the function most employers expect you to know.
The condition can be a value to match, a comparison such as “>100” or “<>0”, a wildcard pattern, or a cell reference. Text matches ignore case. When you need two or more conditions at once, step up to COUNTIFS.
COUNTIF always looks at one range and one condition. Everything about the condition is text, even when it is a number comparison. That is why operators sit inside the quotes and why a cell reference has to be glued on with an ampersand.
Two relatives to know by name: COUNTIFS takes several range and condition pairs and counts the rows that satisfy all of them. SUMIF does the same match but adds a second column instead of counting.
COUNTIF examples
1. Count rows that match a word
| A | B | C | |
|---|---|---|---|
| 1 | Region | Sale | |
| 2 | West | 450 | 3 |
| 3 | East | 900 | |
| 4 | West | 620 | |
| 5 | North | 310 | |
| 6 | West | 275 | |
| 7 | East | 120 |
COUNTIF looks at each cell in A2:A7 and counts the ones equal to "West". Three of the six rows are West.
Matching ignores case, so "west" and "WEST" would count too.
2. Count numbers over a threshold
| A | B | C | |
|---|---|---|---|
| 1 | Region | Sale | |
| 2 | West | 450 | 4 |
| 3 | East | 900 | |
| 4 | West | 620 | |
| 5 | North | 310 | |
| 6 | West | 275 | |
| 7 | East | 120 |
A comparison goes inside the quotes, with the operator first. Four sales exceed 300. To compare against a cell instead of typing the number, join the operator and the cell reference with an ampersand.
3. Wildcards for partial text
| A | B | |
|---|---|---|
| 1 | Product | |
| 2 | Desk Pro | 2 |
| 3 | Lamp | |
| 4 | ProPlan Chair | |
| 5 | Mat |
An asterisk stands for any run of characters. The pattern "*Pro*" counts any product name that contains "Pro". There are two: Desk Pro and ProPlan Chair. Use a question mark to stand for exactly one character.
Common COUNTIF mistakes
- Forgetting the quotes around operators.
=COUNTIF(B2:B7,>300)is a syntax error. The criterion is a string: ">300". - Joining a cell reference wrongly.
">E1"compares against the text "E1", not the number in the cell. Write">"&E1. - Using COUNTIF with two conditions. COUNTIF takes exactly one. For two or more, use COUNTIFS. Adding two COUNTIFs double-counts rows that match both.
- Counting numbers stored as text. Criteria "100" matches both the number 100 and the text 100, but ">50" ignores text. If counts look low, check for text numbers.
COUNTIF on Excel job tests
A prompt for one counted condition sounds like "how many orders are from the West region" or "count the scores above 70". Type the criterion as text in quotes, and when the comparison value lives in a cell, join the operator to it with an ampersand.
If the count looks one or two off, suspect the data: a trailing space after a region name, or numbers stored as text. For dates, build the criterion with DATE or point at a cell holding the date rather than typing a date string, since typed strings depend on the computer's regional settings.
Anything with two conditions, such as West and over $300, needs COUNTIFS.
Related functions
COUNTIF questions
- Is COUNTIF case sensitive?
- No. "west" matches "West". For case-sensitive counting, use SUMPRODUCT with EXACT.
- How do I count cells that are not equal to a value?
- Use the not-equal operator inside the quotes:
=COUNTIF(A2:A7,"<>West"). Note that this also counts blank cells. - How do I count between two numbers?
- Use COUNTIFS with two conditions on the same range:
=COUNTIFS(B2:B7,">=100",B2:B7,"<=500"). - Can COUNTIF count blank cells?
- Yes, with "" as the criterion, though COUNTBLANK is clearer.
Practice COUNTIF
Try it in a working grid: the free lesson COUNTIF: count what matches (module 8 of Everyday Excel) has checked COUNTIF exercises.
Practice this function