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, ...])
πŸ’‘ Best Practice: Always try complex formulas on a copy of your file first before applying them to active production data.

Technical Overview & Engine Mechanics

The FILTER function extracts matching rows or columns from a source dataset based on one or more Boolean condition rules. Operating as a native dynamic array function, FILTER evaluates criteria expressions across rows and outputs only records that return TRUE. Unlike the UI-based Filter menu which alters the visual view for all concurrent collaborators, the FILTER formula outputs a clean, independent sub-table that can be sorted, aggregated, or formatted without modifying the master dataset.

Formula Comparison: FILTER vs. Alternatives

Compared to QUERY, FILTER has two distinct advantages: it handles mixed-type data (numbers and text in the same column) without dropping minority records as nulls, and it allows intuitive multiple-condition filtering using standard boolean logic. Compared to manual copying, FILTER updates dynamically in real time whenever underlying master records are altered.

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.

Example 4: Multi-Condition Filtering with Date and Status Thresholds

=FILTER(A2:E500, (C2:C = "EMEA") * (D2:D >= 5000) * (E2:E = "Approved"))
How it works: Extracts all EMEA regional deals with deal value $5,000+ that have achieved Approved status.

Example 5: OR Logic Filtering Across Multiple Departments

=FILTER(A2:D200, (B2:B = "Engineering") + (B2:B = "Product") + (B2:B = "Design"))
How it works: Using plus (+) creates OR conditions, extracting personnel belonging to any of the three technical departments.

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").
❌ #N/A No matches are found in FILTER evaluation
Resolution: Wrap your formula in IFERROR: =IFERROR(FILTER(range, condition), "No matching records found").
❌ #VALUE! Filter range and condition dimensions must match
Resolution: The height of the condition range must match the source data range exactly. For example, if data is A2:D100, condition must be B2:B100, not B1:B100.

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: FILTER

How do I create AND conditions in FILTER?

Supply additional condition arguments separated by commas, or multiply conditions: =FILTER(A2:D100, B2:B = "Won", C2:C > 1000).

How do I create OR conditions in FILTER?

Add condition expressions using the plus (+) operator: =FILTER(A2:D100, (B2:B = "East") + (B2:B = "West")).

Can FILTER select only specific columns rather than the entire range?

Yes. Wrap FILTER in CHOOSECOLS: =CHOOSECOLS(FILTER(A2:E100, C2:C = "Active"), 1, 2, 5).

Does FILTER alter the original source spreadsheet data?

No. FILTER is strictly read-only. It reads source cells and projects results into a new location without modifying source values.

How do I sort the results of a FILTER formula?

Wrap FILTER inside SORT: =SORT(FILTER(A2:D100, C2:C = "Active"), 4, FALSE) to sort descending by column 4.

Related Google Sheets Formulas