Excel COUNTIF function: count cells that meet a condition
COUNTIF counts the cells in a range that meet one condition. =COUNTIF(B2:B7,">500") counts sales over 500. =COUNTIF(E2:E7,"East") counts rows in the East region.
The examples use this small sales table, the same one in the practice box below.
| A Rep | B Sales | D Target | E Region | |
|---|---|---|---|---|
| 2 | Dana | 1,250 | 1,000 | East |
| 3 | Luis | 830 | 1,000 | West |
| 4 | Priya | 2,100 | 1,000 | East |
| 5 | Marcus | 475 | 1,000 | North |
| 6 | Jen | 990 | 1,000 | West |
| 7 | Omar | 1,600 | 1,000 | East |
Syntax
=COUNTIF(range, criteria)The criteria can be a number, text, a cell or a comparison in quotes.
Examples
| Formula | Result | What it counts |
|---|---|---|
=COUNTIF(B2:B7,">500") | 5 | Sales over 500 |
=COUNTIF(B2:B7,">1000") | 3 | Sales over 1,000 |
=COUNTIF(E2:E7,"East") | 3 | Rows in East |
=COUNTIF(E2:E7,"West") | 2 | Rows in West |
Using a cell in the condition
To count sales above the target in D2, join the operator to the cell with &: =COUNTIF(B2:B7,">"&D2). Writing ">D2" looks for the text "D2" and returns 0.
Two conditions: COUNTIFS
=COUNTIFS(E2:E7,"East",B2:B7,">1000") counts East sales over 1,000. Add range and condition pairs for each rule. All ranges must be the same size.
Common mistakes
- Using COUNT, which counts every number and ignores the condition.
- Forgetting the quotes around
">500". - Typos in text conditions. "east " with a trailing space won't match "East". Clean the data with TRIM first.
Try it: Sales over 500
Count how many reps sold more than 500. Put the count in B8.
Graded live, like a real Excel test. Press New numbers to prove you didn't memorize the answer. More practice
Questions people ask
Is COUNTIF case sensitive?
No. "east" and "East" match the same cells.
Can COUNTIF use wildcards?
Yes. =COUNTIF(A2:A7,"J*") counts names that start with J. Use ? for a single character.
Practice on a real spreadsheet
CellReady grades your formula the moment you press Enter and tells you why a wrong answer is wrong. The first 7 lessons are free, with no sign-up to start.
Related guides
How to use SUMIF in Excel, the argument order that trips people up, using cell references in the condition, and a live exercise.
How the Excel IF function works, with text and number results, AND and OR, nested IFs, the #NAME? mistake, and a live exercise.
A one-page cheat sheet of the Excel formulas that show up on pre-employment tests, with the syntax, a real example and the mistake to avoid for each.