Syntax
COUNTIFS(range1, criteria1, ...)
| Parameter | Description |
|---|---|
| range1 | Parameter of the COUNTIFS function. |
| criteria1 | Parameter of the COUNTIFS function. |
| ... | Parameter of the COUNTIFS function. |
Examples
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Region | Sales | Status | Date |
| 2 | East | 1200 | Shipped | 2024-01-15 |
| 3 | West | 800 | Pending | 2024-02-20 |
| 4 | East | 2100 | Shipped | 2024-03-05 |
| 5 | East | 450 | Cancelled | 2024-01-28 |
Count orders by region and status
Formula
=COUNTIFS(A2:A100, "East", C2:C100, "Shipped")
Returns: 14 — the number of rows where region is "East" AND status is "Shipped"
Count within a date range
Formula
=COUNTIFS(D2:D100, ">="&DATE(2024,1,1), D2:D100, "<"&DATE(2024,4,1))
Returns: 23 — counts entries dated in Q1 2024 (January through March)
Count values in a numeric range
Formula
=COUNTIFS(B2:B100, ">=80", B2:B100, "<=90")
Returns: 12 — counts scores between 80 and 90 inclusive
Common Errors
#VALUE!
The criteria ranges have different sizes. All ranges in COUNTIFS must have the same number of rows and columns.
Returns 0
No rows match ALL criteria simultaneously. Check each criterion individually with COUNTIF to see which one is too restrictive.
Tips
Same column, two criteria
Use the same range twice for between: =COUNTIFS(B:B, ">=10", B:B, "<=20") counts values from 10 to 20.
Cell references in criteria
For dynamic criteria: =COUNTIFS(A:A, F1, B:B, ">"&G1) uses cell values instead of hardcoded criteria.
OR logic workaround
COUNTIFS uses AND logic. For OR, add separate COUNTIFS: =COUNTIFS(A:A,"East",B:B,">100")+COUNTIFS(A:A,"West",B:B,">100").
Try COUNTIFS in Viztab
Import your data and use COUNTIFS with 370+ other formulas. No signup required.
Open Viztab