FILTER Function in Google Sheets
FILTER Function in Google Sheets is a Google Sheets function that filter returns all columns for rows where both conditions are true. Formula Genius generates and validates this formula automatically from a plain-English prompt.
FILTER returns rows from a range that meet your conditions. Simpler than QUERY for straightforward filtering.
The Formula
"Get all rows where the status is Active and the amount is over 1000"
=FILTER(A2:D100,(B2:B100="Active")*(C2:C100>1000))FILTER returns all columns for rows where both conditions are TRUE. Multiplying conditions together creates AND logic (both must be true).
Step-by-Step Breakdown
- A2:D100 is the full data range to return
- B2:B100="Active" creates TRUE/FALSE array for status
- C2:C100>1000 creates TRUE/FALSE array for amount
- Multiplying (*) = AND logic — both must be TRUE
- For OR logic, use + instead of *
Edge Cases & Warnings
- Returns #N/A if no rows match — wrap with IFERROR
- All condition ranges must be the same height as the data range
- Results spill dynamically — the output area must be clear
- Cannot reference the filtered output in further calculations easily
Examples
"Status="Active", Amount>1000"
All active, high-value rows"Status="Active" OR Status="Pending""
=FILTER(A2:D100,(B2:B100="Active")+(B2:B100="Pending"))Frequently Asked Questions
FILTER vs QUERY — which should I use?
FILTER is simpler for basic filtering. Use QUERY when you need grouping (GROUP BY), aggregation (SUM, AVG), or complex sorting that FILTER can't do.
Does FILTER work in Excel?
Yes, Excel 365 has FILTER with identical syntax. It's one of the new dynamic array functions.
Can't find what you need?
Describe any formula in plain English and Formula Genius will generate, explain, and validate it — instantly.