Returns a filtered version of the source range, returning only rows or columns that meet the specified conditions.
Examples
Filters a range using different conditions.
Sample Usage
FILTER(A2:B26, A2:A26 > 5, D2:D26 < 10)
FILTER(A2:C5, {TRUE; TRUE; FALSE; TRUE})
FILTER(A2:B10, NOT(ISBLANK(A2:A10)))
Syntax
FILTER(range, condition1, [condition2, ...])
-
range
- The data to be filtered. -
condition1
- A column or row containing true or false values corresponding to the first column or row ofrange
, or an array formula evaluating to true or false. -
condition2 ...
- [ OPTIONAL ] - Additional rows or columns containing boolean valuesTRUE
orFALSE
indicating whether the corresponding row or column inrange
should pass throughFILTER
. Can also contain array formula expressions which evaluate to such rows or columns. All conditions must be of the same type (row or column). Mixing row conditions and column conditions is not permitted.condition
arguments must have exactly the same length asrange
.
Notes
-
FILTER
can only be used to filter rows or columns at one time. In order to filter both rows and columns, use the return value of oneFILTER
function asrange
in another. -
If
FILTER
finds no values which satisfy the provided conditions,#N/A
will be returned.
See Also
UNIQUE
: Returns unique rows in the provided source range, discarding duplicates. Rows are returned in the order in which they first appear in the source range.