Google Sheets formula workflow

Google Sheets FILTER Function Examples

Use these Google Sheets FILTER function examples when you need matching rows fast, with clear patterns for AND, OR, date filters, dynamic references, sorting, and empty results.

Key point

FILTER syntax: =FILTER(range, condition1, [condition2, ...])

Copy-paste formulas

Single condition

=FILTER(A2:D100, B2:B100="Paid")

Returns rows where the status column equals Paid.

Multiple conditions with AND

=FILTER(A2:D100, B2:B100="Paid", C2:C100>100)

Each condition argument must be true for a row to return.

OR logic

=FILTER(A2:D100, (B2:B100="Paid")+(B2:B100="Pending"))

Adding condition arrays creates OR logic in Google Sheets.

Filter with a cell reference

=FILTER(A2:D100, B2:B100=F1)

Use this when F1 contains the status, region, or category to match.

FILTER Syntax in 30 Seconds

FILTER returns rows from a range when each condition array matches the same row height as the source range.

Base FILTER syntax

=FILTER(range, condition1, [condition2, ...])

Single-Condition FILTER Examples

Single-condition filters are best for status, region, category, amount, date, blank, and text-search checks.

Equal to text

=FILTER(A2:D100, B2:B100="Paid")

Greater than number

=FILTER(A2:D100, C2:C100>100)

Date condition

=FILTER(A2:D100, A2:A100>=DATE(2026,1,1))

Not blank

=FILTER(A2:D100, B2:B100<>"")

Contains text

=FILTER(A2:D100, ISNUMBER(SEARCH("Widget", D2:D100)))

FILTER with Multiple Conditions Using AND

Put each condition in a separate argument when every condition must be true.

AND conditions

=FILTER(A2:D100, B2:B100="Paid", C2:C100>100)

FILTER with OR Logic

For OR logic, wrap each condition in parentheses and add the arrays together.

Do not use OR(B2:B100="Paid",B2:B100="Pending") inside FILTER. OR() reduces the whole column test to one TRUE or FALSE value, but FILTER needs a row-by-row condition array.

OR conditions

=FILTER(A2:D100, (B2:B100="Paid")+(B2:B100="Pending"))

Three allowed statuses

=FILTER(A2:D100, (B2:B100="Paid")+(B2:B100="Pending")+(B2:B100="Review"))

FILTER with blanks and optional fields

Blank cells are not errors by themselves. They disappear only when your condition asks for a filled value or a specific status.

If blank optional fields should stay in the result, add the blank condition explicitly. This is common with form exports, manual review columns, and rows that have not been assigned a final status yet.

Keep Paid or blank status rows

=FILTER(A2:D100, (B2:B100="Paid")+(B2:B100=""))

Add a no-match fallback

=IFNA(FILTER(A2:D100, (B2:B100="Paid")+(B2:B100="")), "No matches")

FILTER with MATCH or lookup-style conditions

Use MATCH when the allowed values live in another range, such as a small list of statuses in F1:F3.

Because MATCH returns #N/A for non-matching rows, convert it into TRUE/FALSE before passing it to FILTER.

Keep rows matching any allowed value

=FILTER(A2:D100, ISNUMBER(MATCH(B2:B100, F1:F3, 0)))

Exclude rows from a blocked list

=FILTER(A2:D100, ISNA(MATCH(B2:B100, F1:F3, 0)))

FILTER Multiple Columns

FILTER can return a full multi-column range as long as the condition ranges have matching row heights.

Multiple columns

=FILTER(A2:F100, B2:B100="East", F2:F100<>"")

FILTER with Cell References

Cell references make the filter easier to reuse when a teammate changes the target value.

Dynamic cell reference

=FILTER(A2:D100, B2:B100=F1)

FILTER and SORT Together

Wrap FILTER in SORT when you want the matching rows returned in a predictable order.

FILTER plus SORT

=SORT(FILTER(A2:D100, B2:B100="Paid"), 1, TRUE)

FILTER Top N Results

Use LARGE or SORTN when the filtered result needs a top-N cutoff.

FILTER plus LARGE or Top N

=FILTER(A2:D100, C2:C100>=LARGE(C2:C100, 5))

IFNA wrapper for no matching results

=IFNA(FILTER(A2:D100, B2:B100="Paid"), "No matches")

FILTER vs QUERY

Use FILTER when you want the original rows that match simple conditions. Use QUERY when you also need SELECT, WHERE, ORDER BY, FORMAT, or date literals in one report formula.

For date-heavy reporting, compare this page with the Google Sheets QUERY date formulas page.

Sample data for FILTER examples

Date	Status	Amount	Product
2026-01-04	Paid	420	Widget
2026-01-12	Pending	310	Widget
2026-02-03	Paid	275	Gadget
2026-02-15		640	Widget

Returned results from the sample data

Formula pattern	Returned result	Why it matters
=FILTER(A2:D100, B2:B100="Paid")	Returns the two Paid rows: 2026-01-04 Widget and 2026-02-03 Gadget.	Shows the simplest status filter result.
=FILTER(A2:D100, (B2:B100="Paid")+(B2:B100="Pending"))	Returns three rows and excludes the blank status row.	Shows row-by-row OR logic without OR().
=FILTER(A2:D100, (B2:B100="Paid")+(B2:B100=""))	Returns the two Paid rows plus the blank-status Widget row.	Shows how to keep optional blank values deliberately.
=FILTER(A2:D100, ISNUMBER(MATCH(B2:B100, F1:F3, 0)))	Returns rows whose Status appears in the allowed list.	Shows why MATCH must be converted to TRUE/FALSE.

Fix FILTER #N/A, #REF, and #VALUE Errors

Error	Common cause	How to fix it
#N/A	No rows match the conditions.	Wrap the formula with IFNA, such as =IFNA(FILTER(A2:D100, B2:B100="Paid"), "No matches").
#REF!	The spill output is blocked by existing cells.	Clear the cells where the result needs to spill.
#VALUE!	The condition range size does not match the filtered range.	Use condition ranges with the same row height as the source range.
Mismatched range sizes	One condition starts or ends on a different row.	Align ranges such as A2:D100 and B2:B100.
OR() returns too many or too few rows	OR() collapses a condition array into one TRUE or FALSE value.	Use row-by-row OR logic such as (B2:B100="Paid")+(B2:B100="Pending").
MATCH inside FILTER returns #N/A	Rows that do not match the lookup list produce #N/A inside the condition array.	Wrap MATCH with ISNUMBER or ISNA before FILTER uses it.
Blank values disappear unexpectedly	The condition excludes empty optional fields.	Add an explicit blank condition when blanks should remain in the result.
Empty result handling with IFNA	FILTER has no matching rows and returns #N/A by default.	Use =IFNA(FILTER(A2:D100, B2:B100="Paid"), "No matches").

FILTER use case table

Use case	Formula pattern	When to use it
One status	=FILTER(A2:D100, B2:B100="Paid")	Use for simple status, owner, region, or category filters.
AND conditions	=FILTER(A2:D100, B2:B100="Paid", C2:C100>100)	Use when every condition must be true.
OR conditions	=FILTER(A2:D100, (B2:B100="Paid")+(B2:B100="Pending"))	Use when any listed condition can match.
OR conditions without OR()	=FILTER(A2:D100, (B2:B100="Paid")+(B2:B100="Pending")+(B2:B100="Review"))	Use plus signs between condition arrays instead of the OR function.
Keep blanks as allowed values	=FILTER(A2:D100, (B2:B100="Paid")+(B2:B100=""))	Use when blank optional status cells should remain visible.
Allowed values list	=FILTER(A2:D100, ISNUMBER(MATCH(B2:B100, F1:F3, 0)))	Use when a row can match any item from a short list.
No matches fallback	=IFNA(FILTER(A2:D100, B2:B100="Paid"), "No matches")	Use when the sheet should show a friendly empty state.

Related tools and guides

FAQ

How do I use FILTER with multiple conditions?

Add each condition as another argument. In Google Sheets, =FILTER(A2:D100, B2:B100="Paid", C2:C100>100) means both conditions must be true.

How do I use OR logic in FILTER?

Add the condition arrays together, such as =FILTER(A2:D100, (B2:B100="Paid")+(B2:B100="Pending")).

Why does OR() not work inside FILTER conditions?

OR() collapses an entire array to one TRUE or FALSE value. FILTER needs a row-by-row Boolean array, so use (range="A")+(range="B") for OR logic.

How do I filter dates in Google Sheets?

Compare the date range with real dates or DATE(year,month,day), for example =FILTER(A2:D100, A2:A100>=DATE(2026,1,1)).

Why is my FILTER formula not working?

The most common causes are no matching rows, mismatched range sizes, blocked spill space, or conditions that compare text to numbers or dates.

How do I keep blank rows or optional fields from breaking FILTER?

Decide whether blanks should match. For required nonblank fields use range<>""; for optional conditions, test the strict condition first and then add a blank-compatible condition when needed.

What is the difference between FILTER and QUERY?

FILTER is usually simpler for returning matching source rows. QUERY is better when you need SELECT, WHERE, ORDER BY, FORMAT, or report-shaped output.

Can FILTER pull data from another sheet?

Yes. Put the sheet name before both the data range and condition ranges, for example =FILTER('Sheet 2'!A2:D100, 'Sheet 2'!B2:B100="Paid").

What is the FILTER formula in Google Sheets?

The FILTER formula returns the rows of a range that meet one or more conditions. The syntax is =FILTER(range, condition1, [condition2, ...]).

Can FILTER do an advanced filter with multiple criteria?

Yes. Add more condition arguments for AND logic, add condition arrays together for OR logic, and wrap the result in SORT or IFNA to control ordering and empty results.