Data Transformation FormulaGoogle Sheets V8 Engine Verified

Google Sheets FILTER Formula: Dynamic Conditional Range Filtering

FILTER dynamically outputs only the rows or columns from a source range that satisfy one or more logical conditions. As source data updates, filtered results re-calculate automatically.

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

Formula Parameters & Arguments

ArgumentTypeRequiredDescription
rangeRangeRequiredThe data range to filter.
condition1Array of BooleansRequiredA column range comparison returning TRUE/FALSE (e.g. B2:B = "Closed").

Real-World Spreadsheet Examples

Explore verified operational formulas used in enterprise reporting and database workflows:

Example 1: Filter Open Tasks Assigned to Specific Team Member

=FILTER(A2:E100, B2:B = "In Progress", C2:C = "Alex")
How it works: Returns only in-progress tasks assigned to Alex.

Example 2: OR Condition Filtering (Multiple Categories)

=FILTER(A2:D50, (B2:B = "SaaS") + (B2:B = "Agency"))
How it works: Uses addition `+` to represent logical OR.

Example 3: Filter by Date Range (Current Quarter)

=FILTER(A2:D, C2:C >= DATE(2026, 10, 1), C2:C <= DATE(2026, 12, 31))
How it works: Isolates Q4 2026 transactions dynamically.

Common Errors & Solutions

❌ #N/A No matches are found in FILTER evaluation
Resolution: Wrap in IFERROR: =IFERROR(FILTER(A2:C, B2:B="XYZ"), "No records found").

Performance Pro Tips

  • In FILTER conditions, multiply `*` signifies logical AND, while addition `+` represents logical OR.
  • Condition ranges must have the exact same number of rows as the filtered source range.

Frequently Asked Questions

Does FILTER recalculate automatically when data changes?

Yes. Google Sheets recalculates all dependent formulas immediately whenever referenced input cells are modified.

Is this formula compatible with Microsoft Excel?

Modern functions like XLOOKUP, FILTER, UNIQUE, SORT, and LET are natively supported in Excel 365. However, QUERY is unique to Google Sheets and uses the Google Visualization API syntax.

Related Google Sheets Formulas