Description
Use this function to extract rows from a range or array that meet specified conditions.
Syntax
FILTER(array, include, [if_empty])
Inputs
This function accepts the following argument:
| Name | Required | Description | Valid input |
|---|---|---|---|
array |
Yes | The range or array to filter. | A cell, a cell range, a dynamic array, or a formula which results in one of these. |
include |
Yes | A Boolean array or logical expression to determine which rows to return. | A logical value (TRUE or FALSE), a number (0 is FALSE, other numbers are TRUE), a reference to a cell containing one of these values, or a formula which results in one of these. |
if_empty |
No | Value to return if no rows match the filter condition. | A text string to be displayed if no matching rows are found. |
Examples
Sample data
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Name | Age | City | Salary |
| 2 | John | 35 | New York | 75000 |
| 3 | Alice | 28 | Chicago | 65000 |
| 4 | Bob | 42 | Los Angeles | 85000 |
| 5 | George | 22 | Denver | 55000 |
| 6 | Helen | 30 | Scottsdale | 71000 |
Sample formulas
Ranges must match! If you are using multiple ranges with FILTER, the row values must be the same for all ranges specified as part of the filter expression.
| Use case | Formula | Result and Explanation | ||||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| Create a dataset of all employees over 30. |
=FILTER(A1:D6, B1:B6>30)
|
This formula examines the dataset (A1:D6) and then extracts the rows where Age is greater than 30.
|
||||||||||||||||||||||||||
| Create a dataset of all employees over 50, and have a custom message if there are none. |
=IFERROR(FILTER(A2:D6, B2:B6>50), "None")
|
This formula uses FILTER to examine the dataset (A2:D6) and then extracts the rows where Age is greater than 50. As there are none, this generates an error, which is handled by IFERROR to display the string "None". Using the data set above, this produces the following:
|
||||||||||||||||||||||||||
| Create a dataset of all employees over 25, sorted by salary in descending order. |
=SORT(FILTER(A2:D6, B2:B6>25), 4, -1)
|
This formula uses FILTER to examine the dataset (A1:D6)
and
then extracts the rows where Age is greater than 25. Using the data set above, this produces the following:
|
||||||||||||||||||||||||||
| Find employees earning more than the average salary. |
=FILTER(A2:D6, D2:D6> AVERAGE(D2:D6))
|
This formula determines the AVERAGE of the salaries (D2:D6) and then uses FILTER to examine the dataset (A2:D6) and extracts those rows where the salary is greater than the average. Using the data table above, this produces the following:
|
||||||||||||||||||||||||||
| Identify employees who are under 30 AND earn more than 60,000. |
=FILTER(A1:D6,(B1:B6<30)*(D1:D6>60000))
|
This formula determines the
AVERAGE
of the salaries (D2:D6) and then uses FILTER to examine
the
dataset (A2:D6) and extracts those rows where the salary
is greater than the average.
|
Notes
- As with all array functions, FILTER dynamically populates results across adjacent cells, starting from the cell containing the FILTER function.
- FILTER:
- Supports complex, multi-condition filtering.
- Return rows matching specified logical criteria.
- Supports multiple logical tests using* ("AND") or + ("OR").
For example, a formula to filter a range to show records where the values in column B are "Chocolate" or "Coffee", would be:=FILTER(A2:C100, (B2:B100="Chocolate") + (B2:B100="Coffee")).
Tips
- If you have a header row, and you don't want the headers to be included, start your array values one row down (for example in the above data set, start with A2 rather than A1).
- FILTER can be combined with other functions, including:
- CHOOSEROWS: Further refine filtered results.
- CHOOSECOLS: Select specific columns from filtered data.
- SORT: Order the filtered results.
Related functions
Here are the other array formula functions supported in Workiva spreadsheets.