COUNTIF - Count entries matching one condition
Count staff in a department, results above a target, or labels with a shared prefix.
By the end, you can
- Write exact, comparison, and wildcard criteria.
- Count matching cells instead of adding amounts.
- Recognize when multiple conditions need COUNTIFS.
Before you start: Understand COUNT and text labels in a range.
In this lesson
A count for one reporting rule
HR wants the headcount across Sales subteams. COUNTIF examines one range and counts the cells matching one condition. Use it for approved requests, scores above a pass mark, or department labels beginning with Sales. Each matching cell contributes one, not its numeric amount.
Syntax and arguments
COUNTIF syntax
=COUNTIF(range, criteria)Both arguments are required. There is no sum_range because the result is a count.
| Argument | Purpose | Example |
|---|---|---|
rangeRequired | Cells to test. | B2:B6 |
criteriaRequired | A value, quoted condition, wildcard pattern, or cell containing the requested value. | "Sales*" |
Count both Sales subteams
Department and attendance register
D2fx
3| A | B | C | D | |
|---|---|---|---|---|
| 1 | Employee | Department | Days | Sales headcount |
| 2 | Anika | Sales - Field | 22 | 3 |
| 3 | Dev | Finance | 18 | |
| 4 | Meera | Sales - Inside | 20 | |
| 5 | Rahul | Operations | 21 | |
| 6 | Zoya | Sales - Field | 19 |
ExampleBlue: input · Green: result
Enter in D2
Exact labels, thresholds, and exclusions
Only the Field subteam
=COUNTIF(B2:B6,"Sales - Field")Anika and Zoya have this exact department label.
Result 2
Attendance of at least 20 days
=COUNTIF(C2:C6,">=20")22, 20, and 21 meet the inclusive threshold.
Result 3
Non-Sales departments
=COUNTIF(B2:B6,"<>Sales*")Finance and Operations do not match the Sales prefix.
Result 2
Patterns and comparisons
- Text matching is not case-sensitive. Literal labels and comparison expressions need quotes; a criteria cell address does not.
- * matches any text sequence; ? matches one character. Use ~ before a wildcard to match the actual symbol.
- For a threshold in E2, write ">="&E2 rather than the text ">=E2".
- A count of zero means no cells matched, not necessarily that the range was wrong. Validate unexpected zeros against the source labels.
Common mistakes and fixes
Check your understanding
Choose an answer for instant feedback. You can try again; answers stay only on this page.