SheetsdrillFILTER
FILTER(range, condition1, [condition2, ...]) returns the rows (or columns) of a range where every condition is TRUE, spilling the result into the cells below. Each condition is a range of the same height compared to a value, such as B2:B="West", and multiple conditions act as AND.
Syntax
=FILTER(range, condition1, [condition2, ...])| Part | What it is |
|---|---|
range | The data to filter, for example A2:D100. The whole row is returned for each match. |
condition1 | A column of TRUE/FALSE values with the same number of rows as range, usually a comparison like C2:C100>1000. |
condition2 | Optional extra conditions; all of them must be TRUE (AND). |
Examples
=FILTER(A2:D100, B2:B100="West")Result: Every row where column B is West
Text matching is case insensitive.
=FILTER(A2:D100, B2:B100="West", C2:C100>1000)Result: West rows with an amount above 1000 (AND)
For OR, add the conditions: (B2:B100="West")+(B2:B100="East").
=SUM(FILTER(C2:C100, A2:A100="Ana"))Result: The total of column C for Ana's rows
Same result as SUMIF, but FILTER can feed any function.
Common mistakes
- #N/A No matches are found in FILTER evaluation simply means nothing matched; wrap in IFERROR(FILTER(...), "None") to show a friendly message.
- #VALUE! FILTER has mismatched range sizes means a condition range is a different length from the data range; use the same rows for both, for example A2:D100 with B2:B100.
- Using AND(B2:B="West", C2:C>1000) returns nothing because AND collapses the whole column into one value; multiply the conditions instead or pass them as separate arguments.
Practice FILTER
These drills check your answer on live data, free, no account needed.
- Sum a filtered listAdd up the revenue for the region in F1 by summing a FILTER. Answer in F2.
- Two conditions in FILTERCount the orders from the region in F1 with an amount above F2. Use FILTER with two conditions, then count the result. Answer in F3.
Questions
How do I filter by a partial text match?
Use REGEXMATCH or SEARCH as the condition: =FILTER(A2:C, REGEXMATCH(A2:A, "Inc")) or =FILTER(A2:C, ISNUMBER(SEARCH("inc", A2:A))).
Can FILTER return only some columns?
Yes, filter a narrower range: =FILTER(A2:A100, C2:C100>0) returns just column A. For non-adjacent columns, filter with braces {A2:A100, D2:D100} or use QUERY.
Related
- QUERYHow the Google Sheets QUERY function works: the SQL-like syntax, select and where, group by with sum and count, order by, limit, and the errors people hit.
- SORT and SORTNHow SORT and SORTN in Google Sheets sort data by one or more columns with a formula and return the top or bottom N rows, with examples and common errors.
- UNIQUEHow the UNIQUE function in Google Sheets returns a list of distinct values or rows, how to sort and count them, and the errors people run into.
- REGEXMATCH, REGEXEXTRACT and REGEXREPLACEHow the regex functions in Google Sheets work: test a pattern with REGEXMATCH, pull text out with REGEXEXTRACT and clean it with REGEXREPLACE, with examples.