FILTER function examples for practical spreadsheet problems

The specific problem

`FILTER` returns rows that meet a condition. It is one of the most useful dynamic array functions because it turns a static table into a live subset.

=FILTER(A2:D100, C2:C100="Open")

That returns every row where the status column is Open.

Multiple conditions

Use multiplication for AND logic:

=FILTER(A2:D100, (B2:B100="East")*(C2:C100="Open"))

Use addition for OR logic:

=FILTER(A2:D100, (B2:B100="East")+(B2:B100="West"))

Handling no matches

Add the optional third argument so the sheet does not show a raw error:

=FILTER(A2:D100, C2:C100="Open", "No open rows")

Common mistakes

• Making the include range a different height than the returned range.
• Leaving data in the spill area.
• Mixing AND and OR without parentheses.
• Returning too many columns when a smaller output would be clearer.

Performance notes

Bounded ranges are better than full-column ranges for large models. If the output feeds a dashboard, combine `FILTER` with `CHOOSECOLS` so downstream formulas only process the fields they need.

Google Sheets equivalent

Google Sheets supports `FILTER` with a similar syntax. The main habit transfers directly: define the table, then define the condition arrays.

Practice this skill in Calcu

Calcu’s formula exercises help build the core dynamic-array reflex: think in ranges, not only single cells.

Related formulas: `SORT`, `UNIQUE`, `XLOOKUP`, `CHOOSECOLS`.