What COUNTIF does
COUNTIF counts all cells in a range that meet a specific criterion. Syntax:
=COUNTIF(range, criterion)
Typical questions COUNTIF answers: how many students scored above 80? How many times does class 5A appear in a list? How many entries contain the word "support"?
Simple example
Column A contains class names (5A, 5B, 6A, ...). Count the number of students from class 5A:
=COUNTIF(A2:A100, "5A")
Or referencing a cell that contains "5A" (e.g. E2):
=COUNTIF(A2:A100, E2)
Criteria with comparison operators
The criterion can include comparison operators – in that case it must be written as text in quotes:
=COUNTIF(B2:B100, ">=50")– counts all values of 50 or more=COUNTIF(B2:B100, "<40")– counts all values below 40=COUNTIF(C2:C100, "<>")– counts all non-empty cells
When the threshold is in a cell (e.g. E2), combine the operator with the cell reference:
=COUNTIF(B2:B100, ">="&E2)
COUNTIFS for multiple conditions
COUNTIFS checks multiple conditions at once – all must be true (AND logic). Syntax:
=COUNTIFS(range1, criterion1, range2, criterion2, ...)
Example: how many students in class 5A scored 50 or more?
=COUNTIFS(A2:A100, "5A", B2:B100, ">=50")
Practical use cases in school
- Class summaries: number of students per class, number reaching a certain score per class
- Support needs analysis: how many students in a year group are below a threshold?
- Event planning: how many times has a particular teacher taken a specific event type?
- Quick validation: are all fields in a submission list filled in?
COUNTIF vs. SUMIF
COUNTIF counts how many times a condition is met. SUMIF adds values from another column when a condition is met. Both are useful – for different questions. SUMIF is covered in the next article.