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

E1=COUNTIFS(A2:A6,"North",B2:B6,"Shoes")
ABCDE
1RegionProductSales2
2NorthShoes500
3SouthShoes300
4NorthBags250
5NorthShoes420
6SouthBags180

In plain words: Count the rows where the region in A2:A6 is North and the product in B2:B6 is Shoes.

E1=COUNTIFS(C2:C6,">=200",C2:C6,"<=400")
ABCDE
1RegionProductSales2
2NorthShoes500
3SouthShoes300
4NorthBags250
5NorthShoes420
6SouthBags180

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.

Practice COUNTIFS

Related functions