Statistical · conditional count, many criteria · Nov 2026

The COUNTIFS function,
measured & explained.

Syntax, a live playground, a measured multi-condition gallery, the AND-not-OR trap, and a sourced error library: conditional counts across many criteria.

fx
LookupLab Research
By LookupLab Research·Updated Nov 16, 2026·Engine: HyperFormula 3.5·What COUNTIFS returns
D1
fx
=COUNTIFS(A2:A5, "Apple", D2:D5, "East")
0
measured gallery rows
0
sourced errors fixed
0
platforms checked
▶ Try the playground Try COUNTIFS live ↓See the 8 errors
// 01 — Anatomy

How COUNTIFS works

Counts the rows that meet every criteria pair. There is no sum_range — it counts matches, not values.

=COUNTIFS(criteria_range1, criteria1, [...])
criteria pairsEach range is tested against its criteria; all pairs are AND-ed.
counts rowsA matching row adds 1, whatever its value.
wildcards* and ? work inside quoted criteria.
.bool-moat td.res-text{color:var(--cyan);font-weight:700;font-family:var(--mono)}
// 02 — Multi-condition count

What COUNTIFS actually counts

COUNTIFS counts rows that meet every condition. It counts rows, not values — a matching row adds 1 no matter what is in it. Every row was measured live in HyperFormula. Click any row to load it.

Multi-condition gallery — counts rows, not values

ExpressionResultWhy
=COUNTIFS(A2:A5,"Apple",D2:D5,"East")2Apple AND East
=COUNTIFS(A2:A5,"Apple")2One pair behaves like COUNTIF
=COUNTIFS(A2:A5,"Apple",B2:B5,">120")1Apple AND amount>120
=COUNTIFS(D2:D5,"East",C2:C5,">=15")1East AND qty>=15
=COUNTIFS(B2:B5,">100",B2:B5,"<300")2Same range twice = a between test
=COUNTIFS(A2:A5,"*an*",D2:D5,"West")1Wildcard AND region
=COUNTIFS(A2:A5,"Apple",D2:D5,"West")0No match: 0
=COUNTIFS(A2:A5,"<>Apple",D2:D5,"East")0Not-Apple AND East: none

Between two bounds with one range

ExpressionResultWhy
=COUNTIFS(B2:B5,">100",B2:B5,"<300")2The same range can appear in two pairs
⚠ Conditions are AND-ed. COUNTIFS has no built-in OR. To count Apple OR Banana, add two COUNTIFS: =COUNTIFS(A2:A5,"Apple")+COUNTIFS(A2:A5,"Banana"). Reusing one range in two pairs is how you build a between test.
// 03 — Try it live

Interactive COUNTIFS playground

Edit the table or type any formula — the engine evaluates instantly. Load a preset to see COUNTIFS AND criteria across columns, build a between-test, and return 0 when nothing matches.

A1Sample data — click any cell to edit, the formula re-evaluates live
Result
—
✓ readyHyperFormula 3.5
// 04 — Error library

The real-world error library

8 failure patterns mined from actual user reports on Reddit, Stack Overflow and Super User — each with the authentic symptom, root cause, a fix, and a runnable formula. Not textbook theory.

🔍
// 05 — Cross-platform

Where COUNTIFS works

COUNTIFS is available across modern Excel, Google Sheets and LibreOffice. It has been in Excel since 2007.

PlatformSupportNotesGotcha
✓ Supported full COUNTIFS◐ Partial works with caveatsCompiled from public docs · verify against your build
// Go deeper

Master conditional aggregation

COUNTIFS is one piece. The full course walks the toolkit end to end with live, measured examples.

  • COUNTIF / COUNTIFS — one vs many conditions
  • AND logic and how to fake OR
  • Between-tests with one range twice
  • Wildcards and case-insensitivity
Free while in beta
Browse the function library

No account needed. Every page has a live playground.

// 07 — FAQ

Frequently asked