The FILTER function
Returns the rows of a range that meet a condition, as a list that spills into the cells below.
How to use FILTER
FILTER pulls out the rows you care about and leaves the rest behind. Think of a sales list on a clipboard where you cross out everything except one region: FILTER writes the remaining rows up as a new list.
You give it the range to return and a test for each row. =FILTER(C2:C6,A2:A6="South") returns the sales from the rows where the region is South. The test needs one answer per row, so both ranges must have the same number of rows. To get whole rows back, use the whole table as the first argument: =FILTER(A2:C6,A2:A6="South"). Combine conditions by multiplying for AND and adding for OR: =FILTER(A2:C6,(A2:A6="South")*(B2:B6="Bags")).
The result spills into the cells below and to the right, and it updates when the data changes. If you only need a total, SUMIFS is simpler. For a single match, use XLOOKUP, and to remove duplicates from a list, use UNIQUE.
Syntax
FILTER(array, include, [if_empty])- array
- The range to filter.
- include
- A condition per row, such as A2:A6="South".
- if_empty (optional)
- What to return when no rows match, instead of #CALC!. Excel only: Google Sheets reads extra arguments as more conditions.
Examples
=FILTER(1C2:C6,2A2:A6="South")| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Region | Product | Sales | 300 | |
| 2 | North | Shoes | 500 | 180 | |
| 3 | South | Shoes | 300 | ||
| 4 | North | Bags | 250 | ||
| 5 | North | Shoes | 420 | ||
| 6 | South | Bags | 180 |
In plain words: Return 1the sales in C2:C6 on the rows where 2the region in A2:A6 is South.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Region | Product | Sales | None | |
| 2 | North | Shoes | 500 | ||
| 3 | South | Shoes | 300 | ||
| 4 | North | Bags | 250 | ||
| 5 | North | Shoes | 420 | ||
| 6 | South | Bags | 180 |
Common FILTER mistakes
The list does not appear because other cells are in the way
FILTER needs free cells below and to the right of the formula for its result. Clear whatever is in those cells and the list appears.
FILTER returns an error about mismatched sizes
The test range and the returned range have a different number of rows, such as A2:C6 together with A2:A5. Make both ranges cover the same rows.
AND and OR do not work inside FILTER
AND returns a single TRUE or FALSE for the whole range, not one answer per row. Multiply the tests for AND and add them for OR:
=FILTER(A2:C6,(A2:A6="South")+(A2:A6="North")).No rows match even though the text looks identical
The cells probably have extra spaces, or the numbers are stored as text. Clean the values inside the test:
=FILTER(C2:C6,TRIM(A2:A6)="South").
Practice FILTER
Read the guide
Questions
Does FILTER work the same in Google Sheets?
Mostly. Google Sheets has no if_empty argument: extra arguments are more conditions, and it returns #N/A when no rows match. Wrap it in IFNA to show your own text, such as =IFNA(FILTER(A2:A6,C2:C6>1000),"None").
Why does FILTER give #SPILL!?
Something is in the cells where the result needs to go. Clear the cells below and to the right of the formula and the list appears.
Which versions of Excel have FILTER?
FILTER is in Excel 365, Excel 2021 and later, and Excel for the web. Excel 2019 and older do not have it.
How do I sort the result of FILTER?
Put SORT around it, as in =SORT(FILTER(A2:C6,A2:A6="South"),3,-1), which sorts by the third column, largest first. In Google Sheets the last argument is TRUE or FALSE instead of 1 or -1.
Can FILTER return more than one match?
Yes, that is the big difference from VLOOKUP, which stops at the first match. FILTER returns every row that meets the condition.