Syntax
FILTER(array, include, [if_empty])
| Parameter | Description |
|---|---|
| array | Parameter of the FILTER function. |
| include | Parameter of the FILTER function. |
| [if_empty] | (Optional.) Parameter of the FILTER function. |
Examples
Filter by threshold
Formula
=FILTER(A2:C20, B2:B20>=1000)
Returns all rows from A2:C20 where column B is 1000 or more. Results spill automatically into adjacent cells below.
Multiple criteria
Formula
=FILTER(A2:D50, (B2:B50="East")*(C2:C50>500))
Returns rows where region is "East" AND sales exceed 500. Multiply conditions for AND logic, add for OR logic.
With fallback
Formula
=FILTER(A2:B10, A2:A10="Platinum", "No results")
Returns matching rows, or "No results" if no rows match. The third argument prevents the #CALC! error on empty results.
Common Errors
#CALC!
Occurs when no rows match the criteria and no if_empty fallback is provided. Always provide the third argument to handle this gracefully.
#VALUE!
Occurs when the include array has a different number of rows than the source array.
Tips
AND vs OR logic
Multiply conditions for AND: (cond1)*(cond2). Add conditions for OR: (cond1)+(cond2). Wrap OR conditions in parentheses and use -- or >0 if needed.
Always use if_empty
The third argument prevents #CALC! errors when nothing matches. Use "No results" or an empty string "" as a fallback.
Combine with SORT or UNIQUE
=SORT(FILTER(A:C, B:B>100), 2, -1) filters then sorts by column 2 descending. Nesting dynamic array functions is a powerful pattern.
Try FILTER in Viztab
Import your data and use FILTER with 370+ other formulas. No signup required.
Open Viztab