COUNTIFS in Google Sheets
Counts the rows that meet every one of several conditions. Use the same range twice to count between two limits.
In Google Sheets
COUNTIFS takes the same pairs of range and criterion in Google Sheets, and every range must be the same size.
How to use COUNTIFS
COUNTIFS counts the rows where every condition is true. Think of a staff list: how many people are in Sales and started after January 1? Each condition checks one column, and a row is counted only when it passes all the checks.
Unlike SUMIFS there is no range to add, so the formula is just pairs of range and condition: =COUNTIFS(A2:A10,"North",B2:B10,"Shoes"). Each pair checks its own column, all ranges must have the same number of rows, and you can use up to 127 pairs.
To keep values between two limits, use the same range twice: =COUNTIFS(C2:C10,">=200",C2:C10,"<=400") counts values from 200 up to and including 400. The same trick works for a period of dates. With the limits in cells, write ">="&F1.
With a single condition you can use the shorter COUNTIF. To add up amounts for the rows that match, use SUMIFS.
Syntax
COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...)- criteria_range1
- The cells the first condition is checked against.
- criteria1
- The first condition.
- criteria_range2, criteria2 (optional)
- More range and condition pairs, up to 127.
Examples
=COUNTIFS(1A2:A6,2"North",3B2:B6,4"Shoes")| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Region | Product | Sales | 2 | |
| 2 | North | Shoes | 500 | ||
| 3 | South | Shoes | 300 | ||
| 4 | North | Bags | 250 | ||
| 5 | North | Shoes | 420 | ||
| 6 | South | Bags | 180 |
In plain words: Count the rows where 1the region in A2:A6 is 2North and 3the product in B2:B6 is 4Shoes.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Region | Product | Sales | 2 | |
| 2 | North | Shoes | 500 | ||
| 3 | South | Shoes | 300 | ||
| 4 | North | Bags | 250 | ||
| 5 | North | Shoes | 420 | ||
| 6 | South | Bags | 180 |
Common COUNTIFS mistakes
COUNTIFS rejects my second condition
Every condition needs its own range in front of it. When both conditions are on the same column, repeat the range:
=COUNTIFS(C2:C6,">=200",C2:C6,"<=400").COUNTIFS returns #VALUE!
The ranges are not the same size, as in
=COUNTIFS(A2:A6,"North",B2:B7,"Shoes"). Make every range cover the same rows.COUNTIFS returns 0 when I ask for two values in the same column
The conditions are joined with AND, and no cell is both North and South, so
=COUNTIFS(A2:A6,"North",A2:A6,"South")gives 0. Add two COUNTIF formulas instead:=COUNTIF(A2:A6,"North")+COUNTIF(A2:A6,"South").COUNTIFS counts fewer rows than I expect
One of the conditions is failing on a hidden difference, such as a space at the end of a name or numbers stored as text. Test each pair on its own with COUNTIF to find the one that drops rows.