The FILTER function
Returns the rows of a range that meet a condition, as a list that spills into the cells below.
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
E1
=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.
E1=FILTER(A2:A6,C2:C6>1000,"None")
| 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 |
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").