The COUNTIFS function in Excel
COUNTIFS counts the rows that meet every one of several conditions.
What COUNTIFS does
Each condition is a pair: the cells to check and the test to apply. A row is counted only when it passes all the tests. All the ranges must be the same size.
Syntax
=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...)
- criteria_range1
- The first set of cells to test.
- criteria1
- The first condition, such as "North".
- criteria_range2, criteria2
- Optional. More pairs. A row must pass every pair to be counted.
Worked example
| A | B | C | |
|---|---|---|---|
| 1 | Product | Region | Sales |
| 2 | Lamp | North | 120 |
| 3 | Mug | South | 80 |
| 4 | Desk | North | 150 |
| 5 | Chair | East | 90 |
| 6 | Plant | North | 60 |
| 7 | North over 100 | 2 |
C7 =COUNTIFS(B2:B6,"North",C2:C6,">100")
Three rows are North, but only two of them have sales above 100.
Now you try
A fresh exercise every time. Press Another exercise as often as you like.
Where you will use it
HR
Count full-time staff in one department.
Sales & Ops
Count orders over a value in one region.
Data cleanup
Find rows where a value repeats across two columns.
Common mistakes
- Ranges of different sizes. COUNTIFS returns a #VALUE! error if they do not match.
- Expecting OR logic. COUNTIFS needs every condition to be true. To count either of two conditions, add two COUNTIFS together.
- Leaving the quotes off a comparison such as ">100".
The COUNTIFS course
- 1Count with two conditionsFree practice belowPractise
- 2Count between two valuesComing soon
- 3Count by team and statusComing soon
- 4Spot duplicates across columnsComing soon
- 5Dashboard countsComing soon
Common questions
Can COUNTIFS count rows that match either condition?
Not directly. Add two COUNTIFS together and subtract any rows that match both, so they are not counted twice.
How many conditions can COUNTIFS take?
Up to 127 pairs of range and criteria.