Cellwise

Counting function

Excel COUNTIFS function

COUNTIFS counts rows where every condition you give is true. You supply pairs of a range and a condition. Use it for questions like "how many West orders over $500" or "how many items low on stock in one warehouse".

fx=COUNTIFS(criteria_range1, criteria1, ...)

Arguments

criteria_range1
The first range to check.
criteria1
The condition for that range, like "West" or ">500".

COUNTIFS examples

Every result below was calculated live from this sample sheet.

ABCD
1SKUWarehouseCategoryQty
2HX-100DallasTools42
3HX-101DallasPaint8
4HX-102DenverTools5
5HX-103DenverPaint61
6HX-104DallasTools3
7HX-105DenverTools27
  1. fx=COUNTIFS(B2:B7,"Dallas",C2:C7,"Tools")

    Result: 2

    Tools lines stocked in Dallas. Both conditions must match.

  2. fx=COUNTIFS(B2:B7,"Denver",D2:D7,"<10")

    Result: 1

    Items in Denver with fewer than 10 on hand.

  3. fx=COUNTIFS(D2:D7,">=20",D2:D7,"<=50")

    Result: 2

    Use the same range twice to count between two numbers.

  4. fx=COUNTIFS(B2:B7,"Dallas",C2:C3,"Tools")

    Result: #VALUE!

    Ranges of different sizes return #VALUE!. Every range must have the same shape.

How to use COUNTIFS

  1. Click an empty cell.
  2. Type =COUNTIFS( and select the first range.
  3. Type a comma and its condition in quotes.
  4. Add more range, condition pairs as needed.
  5. Close the bracket and press Enter.

Common COUNTIFS mistakes

Ranges of different sizes

Every criteria range must have the same number of rows and columns, or Excel returns #VALUE!.

Expecting OR logic

COUNTIFS only counts rows where all conditions are true. For "Dallas OR Denver", add two COUNTIFS together.

Dates typed as text

Write date conditions as ">="&DATE(2025,1,1) so Excel compares real dates.

COUNTIFS FAQ

What is the difference between COUNTIF and COUNTIFS?

COUNTIF takes one condition. COUNTIFS takes one or more pairs of range and condition, and counts rows where all of them are true.

How do I count between two dates in Excel?

Use =COUNTIFS(A2:A100,">="&DATE(2025,1,1),A2:A100,"<="&DATE(2025,3,31)).

How do I use COUNTIFS with OR in Excel?

Add two COUNTIFS: =COUNTIFS(B:B,"Dallas")+COUNTIFS(B:B,"Denver"). Or use =SUM(COUNTIFS(B:B,{"Dallas","Denver"})).

Practice COUNTIFS in these lessons

Guides that use COUNTIFS

Related functions

Updated 2026-10-31