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.
=FILTER(range, condition1, [condition2, ...])Technical Overview & Engine Mechanics
Formula Comparison: FILTER vs. Alternatives
Formula Parameters & Arguments
| Argument | Type | Required | Description |
|---|---|---|---|
| range | Range | Required | The data range to filter. |
| condition1 | Array of Booleans | Required | A 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")Example 2: OR Condition Filtering (Multiple Categories)
=FILTER(A2:D50, (B2:B = "SaaS") + (B2:B = "Agency"))Example 3: Filter by Date Range (Current Quarter)
=FILTER(A2:D, C2:C >= DATE(2026, 10, 1), C2:C <= DATE(2026, 12, 31))Example 4: Multi-Condition Filtering with Date and Status Thresholds
=FILTER(A2:E500, (C2:C = "EMEA") * (D2:D >= 5000) * (E2:E = "Approved"))Example 5: OR Logic Filtering Across Multiple Departments
=FILTER(A2:D200, (B2:B = "Engineering") + (B2:B = "Product") + (B2:B = "Design"))Common Errors & Solutions
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
QUERY
The QUERY function is Google Sheets' most versatile formula, executing a SQL-like Google Visualization API Query Language string across spreadsheet tables to filter, aggregate, group, pivot, and sort data in a single formula.
Data TransformationARRAYFORMULA
ARRAYFORMULA enables single spreadsheet formulas to output an entire matrix or column of calculated results automatically. When new rows are entered, ARRAYFORMULA calculates them instantly without requiring manual formula dragging.
Data TransformationCHOOSEROWS
CHOOSEROWS extracts specific rows from an array or range by their index numbers. Supports negative indexing to pull rows from the bottom of a dataset.