COUNTIFS
Counts the rows that match several conditions at once: how many large orders each city has.
COUNTIFS(criteria_range1, criteria1, ...)- criteria_range1
- Where to check the first condition, for example cities in A2:A8.
- criteria1
- The first condition. Then you add a second range and a second condition the same way.
Return value
The number of rows that match all conditions at once. If there are none, the function returns 0.
Examples
Large orders by city
fx
A
B
C
D
E
F
1
2
3
4
5
6
7
8
9
10
City
Amount, $
City
$5,000 and up
Boston
7200
Boston
Denver
3400
Denver
Boston
2600
Denver
5100
Boston
5400
Boston
9800
Denver
1700
Sheet1
Details
COUNTIFS takes pairs: where to check and what condition. A row counts only if all conditions are met.
Both conditions can apply to the same column. That’s how you count values within a range: ">=1000" and "<5000" for the amount column.
Common mistakes
- Ranges of different lengths: the formula returns a #VALUE! error.
- All conditions work as “and”. To count Boston or Denver, add two formulas together.
- The boundary of the condition: ">5000" doesn’t count an order of exactly $5,000, while ">=5000" does.