FILTER
Return the rows of a range where a condition is TRUE — sales over a threshold, one region's orders — as a live result that spills and updates.
Syntax
FILTER(array, include, [if_empty])| Argument | Required | Description |
|---|---|---|
array | Required | The range to take rows from. |
include | Required | A condition with one TRUE or FALSE per row, such as C2:C6>15. Combine conditions with * for AND and + for OR. |
if_empty | Optional | What to return when no row matches. Without it, no match is an error. |
Examples
Every example below was executed against VisiGrid engine 0.51.0 — not
transcribed from another vendor's documentation. Reproduce any of them with vgrid calc.
| Formula | Result | Notes |
|---|---|---|
=F3 | Di | FILTER(A2:A6,C2:C6>15) in F1 spills Bo, Cy, Di and Ed down column F. |
=I1 | 10 | FILTER(A2:C6,B2:B6="East") in G1 returns whole rows — Ana, East, 10 across G1:I1, then Cy and Ed below. |
=J2 | Ed | East AND over 15, written with * — Cy and Ed. |
=K1 | none | Nothing is over 99, so the if_empty value is returned. |
=SUM(FILTER(C2:C6,B2:B6="East")) | 70 | A filtered result can feed another function. |
=INDEX(FILTER(A2:A6,C2:C6>15),1) | Bo | The first match. |
=FILTER(A2:A6,C2:C6>99) | #CALC! No matches |
A live subset
FILTER returns the rows that meet a condition, and keeps returning them: add a
row that qualifies, or change a value so one stops qualifying, and the result
updates. It replaces copying rows out by hand or an AutoFilter that has to be
re-applied.
The result spills — one formula fills as many rows as match. If something is in
the way, the formula shows #SPILL! instead of overwriting it.
Conditions
The condition compares a whole column at once, giving one TRUE or FALSE per row:
=FILTER(A2:A6,C2:C6>15)Combine conditions arithmetically. * means AND, because TRUE × TRUE is the only
product that is 1; + means OR:
=FILTER(A2:A6,(B2:B6="East")*(C2:C6>15))
=FILTER(A2:A6,(B2:B6="East")+(B2:B6="North"))Each condition needs its own brackets, because comparison binds more loosely
than * and +.
When nothing matches
With no matching rows, FILTER returns #CALC! — unless you give it a third
argument, which it returns instead. "none" or "" keep a report readable when
a filter comes up empty.
Using the result
The filtered rows can go straight into another function:
SUM(FILTER(...)) totals them,
COUNTA counts them, INDEX picks
one, and ROWS says how many came back.
Wrapped in SORT or UNIQUE, a
filter becomes a sorted or de-duplicated report in one cell:
=SORT(FILTER(A2:A6,C2:C6>15))
=UNIQUE(FILTER(B2:B6,C2:C6>15))TRANSPOSE turns the result on its side.
Excel compatibility
Matches Excel, including combining conditions with * and +, the if_empty argument, and a #CALC! error when nothing matches.