Excel function guide · Conditional sums & counts
COUNTIF function in Excel
COUNTIF counts how many cells in a range meet one condition, for example how many people are in Sales or how many scores are 70 or more.
Syntax
=COUNTIF(range, criteria)
| Argument | Required? | What it means |
|---|---|---|
| range | Required | The cells to count. |
| criteria | Required | The condition: a value ("Sales"), a cell, a comparison in quotes (">=70") or a wildcard pattern ("*a*"). |
COUNTIF examples
Every example uses this small table. The results are what Excel shows in the cell.
| A | B | C | |
|---|---|---|---|
| 1 | Name | Department | Score |
| 2 | Hana | Sales | 82 |
| 3 | Omar | Finance | 67 |
| 4 | Lucia | Sales | 91 |
| 5 | Tom | HR | 58 |
| 6 | Mei | Sales | 74 |
| 7 | Raj | Finance | 88 |
1. Count a text value
=COUNTIF(B2:B7, "Sales")
Result: 3
Three people are in Sales. Case doesn't matter: "sales" gives the same answer.
2. Count with a comparison
=COUNTIF(C2:C7, ">=70")
Result: 4
Scores of 70 or more: 82, 91, 74 and 88.
3. Count with a wildcard
=COUNTIF(A2:A7, "*a*")
Result: 4
Names containing an a anywhere: Hana, Omar, Lucia and Raj. * stands for any characters.
Try it now
Practise COUNTIF in a live sheet.
Type each formula yourself. Answers are checked by the same engine as the full course. No account needed.
Step 1 of 3
In E3, count the people in the department named in E2.
Common COUNTIF mistakes
Comparing with a cell inside the quotes
"<E5" compares with the text "E5". Join the operator and the cell instead: "<"&E5.
COUNTIF counts, it doesn't add
To total the scores that meet a condition, use SUMIF. COUNTIF only tells you how many cells qualify.
Numbers stored as text
A score typed as text ("82" with a leading apostrophe) won't match ">=70". Convert the column to numbers first.
More than one condition
COUNTIF takes one condition. For department and score together, use COUNTIFS.
Literal * or ?
* and ? are wildcards. To count cells that contain an actual asterisk or question mark, put a tilde before it: "~*".
Practise this in Excel Quest
COUNTIF & SUMIFFree
Course 5 · Conditional Aggregation · 11 min
Count and total only the rows that match — then build a region summary that reconciles to the total.
This lesson is free: create an account and it opens straight away.