COUNTIFS Function in Excel
COUNTIFS counts cells that meet multiple conditions simultaneously — all AND'd together. It's what you reach for when a single COUNTIF isn't enough: "how many active customers in the East region with orders over $500?" One formula, any number of criteria.
What it does: Counts the number of rows where every criteria_range meets its corresponding criteria. All ranges must be the same size. Supports up to 127 range/criteria pairs.
What COUNTIFS does
COUNTIFS walks through every row of your data and asks: does this row satisfy criteria 1? And criteria 2? And criteria 3? Only rows that pass every check are counted. This is AND logic — for OR logic, you add multiple COUNTIFS results together.
Because all criteria ranges must be the same size, COUNTIFS effectively counts rows in a table where multiple columns each meet their conditions. It's the natural way to slice a dataset into cross-tabulated counts.
Syntax breakdown
criteria_range1 Required
The first range to check. Sets the "shape" of what COUNTIFS considers — every additional criteria range must be identical in size and orientation.
criteria1 Required
The condition criteria_range1 must match. Same syntax as COUNTIF: numbers, text in quotes, comparisons in quotes, wildcards, cell references.
criteria_range2, criteria2, ... Optional
Additional range/criteria pairs. Up to 127 pairs supported. Every range must be the same size as criteria_range1. Every criterion works like COUNTIF's — text, numbers, comparisons, wildcards.
5 real-world examples
Example 1: Count active customers in the East region
Column A has status ("Active"/"Inactive"), column B has region:
=COUNTIFS(A2:A1000, "Active", B2:B1000, "East")Result: 64. Only rows where both status is "Active" and region is "East" get counted.
Example 2: Count orders in a date range
Column C has dates. Count orders between Jan 1 and Mar 31, 2026:
=COUNTIFS(C2:C1000, ">="&DATE(2026,1,1), C2:C1000, "<="&DATE(2026,3,31))Result: 128. The same range appears twice with different criteria — Excel handles that fine.
Example 3: Count using cell references for both criteria
Dashboard-style: user picks region in F2 and status in F3:
Result: Live-updates when the user changes either dropdown. Standard pattern for filter-driven dashboards.
Example 4: Count with wildcards and thresholds combined
Count Gmail users whose orders exceeded $500 (email in C, order value in D):
=COUNTIFS(C2:C1000, "*@gmail.com", D2:D1000, ">500")Result: 37. Wildcards work in text criteria; comparisons work on numbers. Mix freely across conditions.
Example 5: Count using OR logic (with two COUNTIFS added)
Count "East" or "West" region customers with Active status. Since COUNTIFS is pure AND, add two calls:
=COUNTIFS(A2:A1000, "Active", B2:B1000, "East") + COUNTIFS(A2:A1000, "Active", B2:B1000, "West")Result: Active customers in either East or West. For many OR values, consider FILTER + COUNTA in modern Excel — cleaner syntax.
Common errors and how to fix them
The classic COUNTIFS error: criteria ranges are different sizes. A2:A1000 paired with B2:B999 fails. Fix: ensure every range covers exactly the same rows and columns.
Most often means no rows satisfy every condition simultaneously — which may be correct. If unexpected, test each condition separately with COUNTIF to see which one is filtering out too many rows.
A criterion may be too permissive. ">0" against a range with negative numbers counts them wrong. Use tighter criteria and test each condition in isolation.
If A2:A1000 is paired with B3:B1001, each row is checked against the wrong "partner" row. Silently produces wrong answers. Always double-check the row numbers when writing multi-criteria formulas.
COUNTIFS vs alternatives
| Approach | Best for | Watch out for |
|---|---|---|
| COUNTIFS | AND conditions across columns | All ranges must be same size; only AND, not OR |
| COUNTIFS + COUNTIFS | OR conditions on one column | Risk of double-counting if criteria overlap |
| SUMPRODUCT | Complex logic, arrays, XOR | Denser syntax, slower on huge data |
| FILTER + COUNTA | Modern Excel, flexible conditions | Excel 365 / 2021+ only |
| PivotTable | Exploring many combinations interactively | Not a formula — requires manual refresh |
Version compatibility
Download the practice workbook
Every example above, plus a dashboard template using dropdowns and COUNTIFS.
Related functions
Frequently asked questions
What's the maximum number of criteria in COUNTIFS?
127 range/criteria pairs. In practice, if you need more than 4-5 conditions, restructure your data or use a PivotTable instead — the formula becomes unreadable.
Can COUNTIFS handle OR logic?
Not within a single call — every condition is AND'd. For OR, add multiple COUNTIFS: =COUNTIFS(A:A,"East") + COUNTIFS(A:A,"West"). For OR conditions on multiple columns, SUMPRODUCT is often clearer.
Why does COUNTIFS return #VALUE!?
Almost always: ranges are different sizes. A2:A1000 and B2:B999 won't work. Also possible: referencing a closed workbook. Fix the range sizes and the error goes away.
Can I use COUNTIFS with dates?
Yes — same pattern as COUNTIF: =COUNTIFS(A:A, ">="&DATE(2026,1,1), A:A, "<"&DATE(2026,2,1)) counts entries in January 2026. The same range can appear twice with different criteria.
Is COUNTIFS case-sensitive?
No, matching text criteria. For case-sensitive multi-criteria counting, use SUMPRODUCT with EXACT: =SUMPRODUCT((EXACT(A:A, "east")) * (EXACT(B:B, "Active"))).
Does COUNTIFS work with entire columns like A:A?
Yes, but it's slower than bounded ranges. Modern Excel handles whole-column references reasonably, but on very large sheets performance suffers. Use Tables (Sales[Region]) for automatic sizing without the whole-column cost.
How do I count where a value is NOT one of several options?
Chain conditions: =COUNTIFS(A:A, "<>East", A:A, "<>West", A:A, "<>South"). Verbose but works. For many exclusions, count the total and subtract: =COUNTA(A:A) - COUNTIFS(A:A, "East") - COUNTIFS(A:A, "West").
Build multi-condition counts in seconds — with the Sheets & Cells AI Add-in
Describe the count you want in plain English: "how many Active customers in East region with orders over $500." The Add-in writes the COUNTIFS with correct range alignment, right inside Excel.
Learn about the Add-in →