The COUNTIFS function

Counts the rows that meet every one of several conditions. Use the same range twice to count between two limits.

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

Read the guide

Questions

What is the difference between COUNTIF and COUNTIFS?

COUNTIF takes one range and one condition. COUNTIFS takes one or more pairs of range and condition and counts the rows that meet all of them.

How do I count between two dates?

Use the date column twice, with the start date in F1 and the end date in F2: =COUNTIFS(A2:A100,">="&F1,A2:A100,"<="&F2).

How do I count rows that match one value or another?

COUNTIFS joins its conditions with AND, so add two formulas: =COUNTIF(A2:A6,"North")+COUNTIF(A2:A6,"South").

How do I count rows where one cell is empty?

Use "" as the condition for the empty cell: =COUNTIFS(A2:A6,"North",B2:B6,"") counts the North rows where column B is empty. Use "<>" for cells that are not empty.

Related functions