Dynamic arrays · conditional rows · Nov 2026

The FILTER function,
measured & explained.

Syntax, a live playground, a measured conditional gallery, AND/OR criteria, the #CALC! trap, and a sourced error library: the dynamic-array FILTER function.

D1
fx
=FILTER(A2:C8,B2:B8="East")
0
measured gallery rows
0
sourced errors fixed
0
platforms checked
// 01 — Anatomy

How FILTER works

Returns the rows of array where include is TRUE, spilled into neighboring cells. Available in Excel 365 / 2021 and Excel Online.

=FILTER(array, include, [if_empty])
arrayThe range or array to filter.
includeA boolean array the same height as array; TRUE keeps the row.
if_emptyValue returned when no rows match (avoids #CALC!).
.bool-moat td.res-text{color:var(--cyan);font-weight:700;font-family:var(--mono)}
// 02 — Conditional rows

What FILTER actually returns

FILTER returns the rows that meet a condition and spills them. The live gallery wraps FILTER in SUM/COUNTA/INDEX so each result is one measurable value. Every row was measured live in HyperFormula. Click any row to load it.

Conditional gallery — measured wrappers

ExpressionResultWhy
=SUM(FILTER(C2:C8,B2:B8="East"))900Total sales for the East region
=COUNTA(FILTER(A2:A8,B2:B8="East"))4How many East rows
=SUM(FILTER(C2:C8,C2:C8>200))950Sales above 200
=INDEX(FILTER(A2:A8,B2:B8="East"),1)AliceFirst East name
=FILTER(A2:A8,B2:B8="East")AliceTop-left of the spill (Excel shows all 4)
=COUNTA(FILTER(A2:A8,C2:C8>=250))3Rows with sales at least 250
=IFERROR(SUM(FILTER(C2:C8,B2:B8="Nope")),0)0No match guarded to 0
=COUNTA(FILTER(A2:A8,(B2:B8="East")*(C2:C8>200)))2East AND sales>200

Combine conditions: * = AND, + = OR

ExpressionResultWhy
=FILTER(A2:A8,(B2:B8="East")*(C2:C8>200))2 rowsEast AND sales>200 (Bob, Dave)
=FILTER(A2:A8,(B2:B8="East")+(B2:B8="West"))7 rowsEast OR West (every row)
=FILTER(A2:C8,B2:B8="East")4 rowsReturn whole rows, not one column
⚠ FILTER with no matching rows returns #CALC! (an empty filter). Pass the 3rd argument if_empty, or wrap in IFERROR: =IFERROR(FILTER(A2:A8,B2:B8="Nope"),"none"). The include argument must be a boolean array the same height as the range.
The include argument is a boolean array: B2:B8="East" produces {TRUE;FALSE;...}. Multiply arrays for AND, add them for OR. FILTER keeps the source order; nest SORT to reorder.
// 03 — Try it live

Interactive FILTER playground

Edit the table or type any formula — the HyperFormula engine evaluates it instantly. Load a preset to total a region, count matching rows, and combine AND/OR criteria.

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 FILTER works

FILTER requires dynamic arrays: Excel 365 / 2021, Excel Online and Google Sheets. Excel 2019 and earlier return #NAME?; use SUMIFS/COUNTIFS there.

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

Master dynamic arrays

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

  • UNIQUE / FILTER / SORT — the dynamic-array trio
  • AND (*) and OR (+) criteria arrays
  • The #CALC! empty-filter error and if_empty
  • SUMIFS/COUNTIFS as the universal alternative
Free while in beta
Browse the function library

No account needed. Every page has a live playground.

// 07 — FAQ

Frequently asked