What COUNTIFS Does
COUNTIFS counts the number of rows where multiple conditions are all met. It extends COUNTIF by allowing two or more criteria, making it ideal for counting records that match a combination of filters — such as orders from a specific region in a specific month.
Syntax
=COUNTIFS(criteria_range1, criterion1, [criteria_range2, criterion2, ...])
| Parameter | Description |
|---|---|
| criteria_range1 | The first range to evaluate. |
| criterion1 | The condition for criteria_range1. |
| criteria_range2, criterion2, ... | Additional range/condition pairs. All conditions must be true for a row to be counted. |
Basic Examples
Example 1: Count by Region and Status
| A | B | C | |
|---|---|---|---|
| 1 | Region | Status | Amount |
| 2 | East | Active | 5000 |
| 3 | West | Closed | 3200 |
| 4 | East | Active | 4100 |
| 5 | East | Closed | 2800 |
=COUNTIFS(A2:A5, "East", B2:B5, "Active")
Result: 2 — rows 2 and 4 match both conditions.
Example 2: Count Within a Numeric Range
How many products are priced between $10 and $50?
=COUNTIFS(C2:C200, ">=10", C2:C200, "<=50")
Both conditions apply to the same column, creating a "between" filter.
Example 3: Count by Date Range
Count orders placed in Q1 2026:
=COUNTIFS(D2:D500, ">="&DATE(2026,1,1), D2:D500, "<="&DATE(2026,3,31))
Advanced Examples
Cross-Tab Summary
Build a matrix showing counts by region and product type. In a summary table, each cell uses COUNTIFS:
=COUNTIFS(Data!A:A, $G2, Data!B:B, H$1)
Where G2 holds the region name and H1 holds the product type. Locking the row ($G2) and column (H$1) references lets you drag this formula across a grid to build a full cross-tab automatically.
Excluding Blanks
Count active customers who have a phone number on file:
=COUNTIFS(B2:B500, "Active", C2:C500, "<>")
The "<>" criterion means "not blank."
Common Mistakes
- Mismatched range sizes. Every criteria range must contain the same number of rows. Mixing
A2:A100withB2:B50causes an error. - Expecting OR logic. COUNTIFS uses AND logic — all conditions must be met. For OR logic (e.g., "East" OR "West"), add two separate COUNTIFS calls:
=COUNTIFS(A:A,"East",B:B,"Active") + COUNTIFS(A:A,"West",B:B,"Active"). - Hardcoding dates as text.
=COUNTIFS(D:D, ">=2026-01-01")may not work reliably. Always use the DATE function:">="&DATE(2026,1,1).
SheetAI Tip
Don't want to memorize this syntax? With SheetAI, just type what you need in plain English — like "count orders from East region that are Active" — and the formula is generated for you. Install SheetAI to try it free.