The Excel COUNTIFS function, explained interactively.
COUNTIFS counts rows that pass every one of multiple conditions — COUNTIF with more filters, AND’d together. Unlike SUMIFS, there is no argument-order surprise: just repeat (range, criteria) for each condition.
How to use COUNTIFS
Pick two filters — only rows matching both contribute to the count. Every extra pair tightens the filter further.
- Type
=COUNTIFS(and select the first criteria_range — the column to test. - Add a comma and the first criteria (the value, comparison, or wildcard the range must match).
- Repeat: every subsequent pair adds another filter. COUNTIFS applies AND logic across all pairs.
- Close the parenthesis. COUNTIFS returns an integer — never an error, zero when nothing passes every test.
"Open" or a negation "<>Closed".| A | B | |
|---|---|---|
| 1 | Status | Priority |
| 2 | Open | High |
| 3 | In Progress | High |
| 4 | Open | Medium |
| 5 | Closed | High |
| 6 | Open | High |
| 7 | In Progress | Low |
| 8 | Closed | Medium |
| 9 | Open | Low |
| 10 | ||
| 11 | COUNTIFS result | 2 |
COUNTIFS syntax and arguments
Up to 127 pairs of criteria_range + criteria. See Microsoft’s official COUNTIFS reference for the canonical specification.
criteria_range must be the same height — if they’re not, COUNTIFS returns #VALUE!.criteria_range1. Accepts exact values ("Open"), comparisons (">=100"), wildcards ("*prod"), and cell references. Operators stay inside the quotes.criteria1. Pairs can test entirely different columns or even different sheets.COUNTIFS examples
Four patterns that cover the overwhelming majority of real COUNTIFS use.
Example 1: COUNTIFS with two exact matches
The canonical case — count rows that satisfy two text conditions on different columns.
Counts rows where column A is Open AND column B is High. Both conditions must pass simultaneously.
Example 2: COUNTIFS with a comparison
Numeric thresholds combined with a text filter.
Counts Food rows where the amount is at least 50. Keep the operator inside quotes. A bare >=50 is a syntax error.
Example 3: COUNTIFS for OR logic (add two calls)
COUNTIFS itself only does AND. For OR, add two COUNTIFS expressions together.
A common alternative is SUMPRODUCT with boolean arrays, but for two or three alternatives the two-COUNTIFS pattern reads best. Avoid double-counting if the alternatives overlap.
Example 4: COUNTIFS driven by cell inputs
Dashboard pattern — let the user change filter values and watch the count recompute.
E1 holds a status (used directly); E2 holds a numeric threshold, concatenated onto the operator with &. Ideal for interactive reports.
Common COUNTIFS errors and fixes
Four failure modes, each with what to check and how to recover.
COUNTIFS returns #VALUE!
Cause: ranges in the formula have different sizes. Every criteria_range must span exactly the same number of cells.
Align the ranges — if the first range is A2:A100, every subsequent range must also be 99 cells tall. Full-column references (A:A, B:B) avoid the mismatch entirely.
COUNTIFS returns 0 unexpectedly
Cause: no row passes every criterion (remember, AND not OR), or a criterion has invisible whitespace.
Drop one criterion at a time and re-run to find which pair excludes everything. If every pair looks right, run TRIM on the source data.
COUNTIFS operator outside the quotes
Cause: wrote COUNTIFS(A:A, >50) instead of quoting the comparison.
Quote the full comparison. For a cell-driven threshold, concatenate operator + reference: ">"&E2.
COUNTIFS double-counting in OR patterns
Cause: added two COUNTIFS for OR logic, but some rows satisfy both branches and got counted twice.
Subtract the intersection: A + B - COUNTIFS(both conditions). Or use SUMPRODUCT with a proper boolean-OR expression to count each row at most once.
COUNTIFS vs COUNTIF, SUMIFS & AVERAGEIFS
The -IFS family applies the same multi-criteria pattern to three aggregates. Pick based on whether you need the sample size, the total, or the mean.
| Function | Criteria | What it returns | Argument order |
|---|---|---|---|
| COUNTIFS | One or many | Count of matching rows | range1, crit1, range2, crit2, … |
| COUNTIF | Exactly one | Count of matching rows | range, criteria |
| SUMIFS | One or many | Sum of matching rows | sum_range, range1, crit1, … (order differs) |
| AVERAGEIFS | One or many | Mean of matching rows | average_range, range1, crit1, … |
Unlike SUMIFS, COUNTIFS has no aggregated range to put first — it simply repeats (range, criteria) pairs. Same mental model as COUNTIF, just with more filters. When you need both the count and the sum, pair COUNTIFS with SUMIFS using identical criteria.
COUNTIFS frequently asked questions
6.01What’s the difference between COUNTIF and COUNTIFS?▸
COUNTIF supports exactly one criterion; COUNTIFS supports up to 127. COUNTIFS keeps the same (range, criteria) order — no argument-order surprise like SUMIFS. Use COUNTIFS the moment you need more than one filter.
6.02Does COUNTIFS use AND or OR logic?▸
AND. Every criterion must pass for a row to count. For OR logic, add two COUNTIFS together or use SUMPRODUCT with boolean arrays.
6.03Can COUNTIFS use comparison operators like >=100?▸
Yes. Put the operator inside quotes: =COUNTIFS(A:A, "Open", B:B, ">=100"). To inject a cell value with an operator, concatenate: ">="&D1.
6.04How many criteria can COUNTIFS take?▸
Up to 127 (range, criteria) pairs. In practice three or four is the readability limit — beyond that a helper column or pivot table reads better.
6.05Why does COUNTIFS return #VALUE!?▸
The ranges have different sizes. Every criteria_range must span exactly the same number of cells. Re-select so all ranges align, or use full-column refs (A:A, B:B) for uniform height.
Microsoft Excel is a registered trademark of Microsoft Corporation. Google Sheets is a trademark of Google LLC. Formula Gym is not affiliated with, endorsed by, or sponsored by either company.