Skip to main content
WebUtilsLabUseful tools, one clean workspace

Google Sheets formula builder

Google Sheets FILTER Formula Builder

Generate Google Sheets FILTER formulas from local inputs. Add conditions, choose operators, handle dates safely, and copy a working formula without uploading a spreadsheet.

Builder inputs

Use the data range to return, then add condition ranges that line up with the same rows.

The rows and columns returned by FILTER.
Choose how active conditions work together. Mixed logic uses exactly three conditions.
Use YYYY-MM-DD for date values.
For A:D, use 1 for A, 2 for B, 3 for C, or 4 for D.
Sheet names with spaces are quoted automatically.

FILTER formula is ready to copy.

Generated FILTER formula
=FILTER(A2:D100,B2:B100="Paid")
What the formula does
FILTER returns rows from the data range when every condition is true. Separate condition arguments act like AND logic in Google Sheets.
Optional QUERY alternative
=QUERY(A2:D100, "SELECT * WHERE B = 'Paid'", 0)
Formula check
No problems found.

How this formula works

  • FILTER returns rows from the data range where each condition evaluates to TRUE.
  • For AND logic, Google Sheets accepts separate condition arguments after the returned range.
  • For OR logic, Google Sheets adds condition arrays together, such as (B2:B100="Paid")+(B2:B100="Pending").
  • For mixed logic, the builder keeps condition 1 required while condition 2 or condition 3 may match.
  • Optional sorting wraps the completed FILTER formula in SORT and uses a column number from the returned range.
  • Date values are generated with DATE(year,month,day) to avoid locale-specific date parsing.
  • Another-sheet formulas quote and escape sheet names before prefixing ranges.

Best fit

Best for

  • Returning and optionally sorting matching rows without writing a QUERY string.
  • Google Sheets dashboards that need a live filtered list by status, owner, region, amount, date, blank cells, or contained text.
  • Tables with clean data ranges and condition ranges that use the same row height.

Not for

  • Selecting and reordering only some columns in Google Sheets. QUERY is often cleaner for that.
  • Older Excel versions without dynamic arrays.
  • Large reporting formulas that need grouping, aggregation, or several report clauses.

Useful formula variations

Google Sheets FILTER with no-match fallback
=IFERROR(FILTER(A2:D100, B2:B100="Review"), "No matches")

The sample has no Review rows, so IFERROR returns the friendly fallback.

Google Sheets FILTER with multiple AND conditions
=IFERROR(FILTER(A2:D100, B2:B100="Paid", C2:C100>=300), "No matches")

The sample returns the Paid row with an amount of 420.

Google Sheets FILTER with OR logic
=FILTER(A2:D100,(B2:B100="Paid")+(B2:B100="Pending"))

Adding Boolean arrays creates OR logic in Google Sheets.

Google Sheets FILTER with mixed AND and OR logic
=FILTER(A2:D100,(B2:B100="Paid")*((D2:D100="Widget")+(D2:D100="Gadget")))

The Paid condition is required, while either listed product may match.

Google Sheets FILTER sorted by amount
=SORT(FILTER(A2:D100,B2:B100<>""),3,FALSE)

The third returned column is sorted from highest to lowest after blank statuses are removed.

Google Sheets FILTER with OR logic, not OR()
=FILTER(A2:D100,(B2:B100="Paid")+(B2:B100="Pending")+(B2:B100="Review"))

Use row-by-row Boolean arrays instead of OR(), which collapses the array to one TRUE or FALSE value.

Google Sheets FILTER contains text
=FILTER(A2:D100,ISNUMBER(SEARCH("Widget",D2:D100)))

SEARCH is case-insensitive and works for text contained inside longer product or notes values.

Google Sheets FILTER keep blanks as an allowed value
=FILTER(A2:D100,(B2:B100="Paid")+(B2:B100=""))

Use this when blank optional fields should stay in the filtered result.

Google Sheets FILTER from a list of allowed values
=FILTER(A2:D100,ISNUMBER(MATCH(B2:B100,F1:F3,0)))

MATCH returns row-level membership, and ISNUMBER converts matches into TRUE values.

Google Sheets FILTER from another sheet
=FILTER('Sheet 2'!A2:D100,'Sheet 2'!B2:B100="Paid")

Quote sheet names with spaces or punctuation.

Sample data

DateStatusAmountProduct
2026-01-04Paid420Widget
2026-01-12Pending310Widget
2026-02-03Paid275Gadget
2026-02-15640Widget

What does FILTER do in Google Sheets?

The Google Spreadsheet FILTER function returns rows or columns from a range when one or more conditions are true. It is useful for live views of paid invoices, open tasks, dates, categories, and text matches.

FILTER function syntax

The core syntax is FILTER(range, condition1, [condition2, ...]). The range is what you want returned, and each condition range must line up with the returned rows or columns.

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

FILTER by one condition

Use one condition when a single status, region, owner, category, or amount rule decides which rows should be returned.

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

FILTER by multiple conditions

Add more condition arguments when every rule should be true. Each condition range should have the same row height as the data range.

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

FILTER with AND logic

In Google Sheets FILTER, separate condition arguments behave like AND logic.

AND logic
=FILTER(A2:D100,B2:B100="Paid",C2:C100>=300)

FILTER with OR logic

For OR logic, add condition arrays together inside a single FILTER condition argument.

Do not use OR(B2:B100="Paid",B2:B100="Pending") here. OR() returns one TRUE or FALSE value for the whole array, while FILTER needs one TRUE or FALSE value for each row.

OR logic
=FILTER(A2:D100,(B2:B100="Paid")+(B2:B100="Pending"))
Three allowed statuses
=FILTER(A2:D100,(B2:B100="Paid")+(B2:B100="Pending")+(B2:B100="Review"))

Detailed guide

The calculator stays focused here. The supporting reference has moved to its own page.

Read the Google Sheets FILTER Formula Builder guide