FILTER Function

Returns a filtered array of data based on criteria you define. A dynamic array function that automatically spills results into multiple cells.

ExcelGoogle Sheets

Syntax

FILTER(array, include, [if_empty])

Parameter	Description
array	The range or array to filter.
include	A Boolean array the same height/width as the array. TRUE values are included.
if_empty(optional)	Value to return if no results match.

Filter sales over $1,000

=FILTER(A2:C100, C2:C100>1000, "No results")

Result: Returns all rows where column C exceeds 1000

Filter by multiple criteria (AND logic)

=FILTER(A2:D20, (B2:B20="East")*(C2:C20>500))

Result: Returns rows where region is East AND amount > 500

Filter by multiple criteria (OR logic)

=FILTER(A2:D20, (B2:B20="East")+(B2:B20="West"))

Result: Returns rows where region is East OR West

•FILTER is a dynamic array function — results spill automatically.
•Use * for AND logic and + for OR logic between criteria arrays.
•Always provide the if_empty argument to avoid #CALC! errors when nothing matches.
•Available in Excel 365 and Google Sheets.

Describe what you need in plain English and our AI will generate the formula for you.