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.
=FILTER(A2:D100,B2:B100="Paid")FILTER returns rows from the data range when every condition is true. Separate condition arguments act like AND logic in Google Sheets.=QUERY(A2:D100, "SELECT * WHERE B = 'Paid'", 0)No warnings.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").
- 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 matching rows quickly when you do not need SQL-like SELECT or ORDER BY clauses.
- 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, sorting, and multiple query clauses.
Useful formula variations
=IFERROR(FILTER(A2:D100, B2:B100="East"), "No matches")Wrap FILTER in IFERROR so empty results show a friendly message.
=IFERROR(FILTER(A2:D100, B2:B100="East", D2:D100>=300), "No matches")Separate condition arguments in Google Sheets act like AND logic.
=FILTER(A2:D100,(B2:B100="Paid")+(B2:B100="Pending"))Adding Boolean arrays creates OR logic in Google Sheets.
=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.
=FILTER(A2:D100,ISNUMBER(SEARCH("Widget",D2:D100)))SEARCH is case-insensitive and works for text contained inside longer product or notes values.
=FILTER(A2:D100,(B2:B100="Paid")+(B2:B100=""))Use this when blank optional fields should stay in the filtered result.
=FILTER(A2:D100,ISNUMBER(MATCH(B2:B100,F1:F3,0)))MATCH returns row-level membership, and ISNUMBER converts matches into TRUE values.
=FILTER('Sheet 2'!A2:D100,'Sheet 2'!B2:B100="Paid")Quote sheet names with spaces or punctuation.
Sample data
| 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 |
What is the Google Spreadsheet FILTER function?
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(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.
=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.
=FILTER(A2:D100,B2:B100="Paid",C2:C100>100)FILTER with AND logic
In Google Sheets FILTER, separate condition arguments behave like AND logic.
=FILTER(A2:D100,B2:B100="Paid",D2:D100>=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.
=FILTER(A2:D100,(B2:B100="Paid")+(B2:B100="Pending"))=FILTER(A2:D100,(B2:B100="Paid")+(B2:B100="Pending")+(B2:B100="Review"))FILTER by date
Use DATE(year,month,day) for date values so the formula does not depend on spreadsheet locale settings.
=FILTER(A2:D100,A2:A100>=DATE(2026,1,15))FILTER contains text
Use SEARCH wrapped in ISNUMBER when the matching text can appear inside a longer cell value.
=FILTER(A2:D100,ISNUMBER(SEARCH("Widget",D2:D100)))FILTER not blank
Use <>"" to return rows where a key column is not empty.
=FILTER(A2:D100,B2:B100<>"")FILTER with blanks and optional fields
If blank values should stay in the result, include the blank condition explicitly instead of treating blanks as an error.
This is useful for form exports where a field is optional but the row should still appear in the filtered result.
=FILTER(A2:D100,(B2:B100="Paid")+(B2:B100=""))=IFNA(FILTER(A2:D100,(B2:B100="Paid")+(B2:B100="")), "No matches")FILTER with MATCH or lookup-style conditions
MATCH is useful when a row can match any item from a small list, but unmatched rows return #N/A. Convert that result into TRUE/FALSE before FILTER uses it.
Use ISNUMBER(MATCH(...)) when you want rows whose status appears in a list of allowed values.
=FILTER(A2:D100,ISNUMBER(MATCH(B2:B100,F1:F3,0)))=FILTER(A2:D100,ISNA(MATCH(B2:B100,F1:F3,0)))FILTER from another sheet
When the source data lives on another tab, prefix both the data range and condition ranges with the sheet name.
=FILTER('Sheet 2'!A2:D100,'Sheet 2'!B2:B100="Paid")FILTER vs QUERY
Use FILTER when you want a readable row filter that returns the original columns. Use QUERY when you need SELECT columns, ORDER BY, grouping, or SQL-like conditions in one query string.
Common FILTER errors and fixes
Most FILTER errors come from mismatched range sizes, invalid date text, missing values, or blocked spill space. The table below maps the common error to the fix.
Sample results from the default data
| Formula pattern | Returned result from the sample rows | Use when |
|---|---|---|
| B2:B100="Paid" | Returns the 2026-01-04 Widget row and the 2026-02-03 Gadget row. | You need rows for one status. |
| B2:B100="Paid", C2:C100>100 | Returns the two Paid rows because both amounts are above 100. | You need AND logic across status and amount. |
| (B2:B100="Paid")+(B2:B100="Pending") | Returns three rows and leaves out the blank-status row. | You need OR logic for allowed statuses. |
| ISNUMBER(SEARCH("Widget",D2:D100)) | Returns three Widget rows, including the blank-status row. | You need a contains-text filter on a product or notes column. |
FILTER use case table
| Use case | Formula pattern | When to use it |
|---|---|---|
| One condition | =FILTER(A2:D100,B2:B100="Paid") | Return rows where one status, owner, region, or category matches. |
| Multiple AND conditions | =FILTER(A2:D100,B2:B100="Paid",C2:C100>100) | Keep rows only when every condition is true. |
| OR logic | =FILTER(A2:D100,(B2:B100="Paid")+(B2:B100="Pending")) | Keep rows when any listed status or category is true. |
| OR logic on the same column | =FILTER(A2:D100,(B2:B100="Paid")+(B2:B100="Pending")+(B2:B100="Review")) | Use when one status column can contain any of several accepted values. |
| Date filter | =FILTER(A2:D100,A2:A100>=DATE(2026,1,15)) | Filter rows after a typed date without locale problems. |
| Contains text | =FILTER(A2:D100,ISNUMBER(SEARCH("Widget",D2:D100))) | Find rows where a word appears inside longer text. |
| Not blank | =FILTER(A2:D100,B2:B100<>"") | Remove rows where a key column is empty. |
| Blank-compatible condition | =FILTER(A2:D100,(B2:B100="Paid")+(B2:B100="")) | Use when blank status cells should be kept with one selected status. |
| Another sheet | =FILTER('Sheet 2'!A2:D100,'Sheet 2'!B2:B100="Paid") | Return rows from a separate tab without copying the source data. |
FILTER error table
| Error | Common cause | How to fix it |
|---|---|---|
| No matches are found | No rows meet the condition. | Use IFERROR if you need a friendly fallback, or test one condition at a time. |
| FILTER has mismatched range sizes | The data range and condition range do not use the same number of rows. | Use aligned ranges such as A2:D100 and B2:B100. |
| Formula parse error | Text quotes, sheet names, or dates are written incorrectly. | Use quoted text, escaped sheet names, and DATE(year,month,day). |
| Date filter returns wrong rows | The formula uses a locale-specific date string. | Use DATE(2026,1,15) instead of text like 1/15/2026. |
| OR formula fails | The OR condition arrays do not line up. | Use condition ranges with the same height. |
| OR() returns too many or too few rows | OR() collapses the array to one value instead of testing each row. | Use (range="A")+(range="B") so FILTER receives a row-by-row Boolean array. |
| MATCH inside FILTER returns #N/A | MATCH returns #N/A for rows that do not match, and the error breaks the Boolean array. | Wrap the MATCH test with ISNUMBER or IFNA before combining it with other FILTER conditions. |
| Blank values disappear unexpectedly | The condition requires a filled value, so blank optional fields do not match. | Add a blank-compatible condition such as (B2:B100="Paid")+(B2:B100="") when blanks should stay. |
| Spill output is blocked | Cells below or beside the formula already contain values. | Clear the output area before entering the formula. |
Troubleshooting
| Problem | Likely cause | Fix |
|---|---|---|
| No matches are found | No rows meet the condition, or the date/text criteria does not match the source values. | Use the fallback output and test one condition at a time before adding more. |
| Range height mismatch | The data range and condition range use different row counts. | Use ranges such as A2:D100, B2:B100, and C2:C100 so the row heights match. |
| Date format is invalid | The date value is not written as YYYY-MM-DD. | Enter dates such as 2026-01-15 so the builder can output DATE(2026,1,15). |
| Another-sheet formula fails | The sheet name is missing or was not quoted. | Enable the another sheet toggle and enter the source tab name. The builder quotes it automatically. |
| OR logic returns an error | The OR condition ranges do not line up. | Use OR condition ranges with the same row height. |
| OR() does not work inside FILTER | OR() returns one TRUE or FALSE value for the whole array instead of one value per row. | Use (B2:B100="Paid")+(B2:B100="Pending") so FILTER receives row-by-row OR logic. |
| FILTER with MATCH returns #N/A | MATCH returns #N/A for values that are not in the lookup list. | Wrap the lookup test with ISNUMBER(MATCH(range,list,0)) for include logic or ISNA(MATCH(range,list,0)) for exclude logic. |
| Blank optional fields are removed | The condition only allows one filled value, so blank rows do not match. | Add a blank condition with OR logic, such as (B2:B100="Paid")+(B2:B100=""). |
Common mistakes
- The condition ranges must align with the rows in the data range.
- Text values need quotes when written manually.
- Blank and not-blank operators do not need a value.
- Date values should use YYYY-MM-DD in the builder so the output can use DATE(year,month,day).
- FILTER spills results, so blocked output cells can cause a spill error.
Related tools and guides
FAQ
How do I use FILTER with multiple conditions?
In Google Sheets, add each AND condition as another FILTER argument, such as FILTER(A2:D100,B2:B100="Paid",C2:C100>100).
How do I use OR logic in FILTER?
Wrap each condition in parentheses and add the condition arrays together, such as FILTER(A2:D100,(B2:B100="Paid")+(B2:B100="Pending")).
How do I filter dates in Google Sheets?
Use DATE(year,month,day) for typed date values, such as A2:A100>=DATE(2026,1,15), to avoid locale-specific date strings.
Why is my FILTER formula not working?
The most common causes are mismatched range heights, missing values, invalid date text, or output cells blocking the spilled results.
What is the difference between FILTER and QUERY?
FILTER is best for direct row filtering. QUERY is better when you need SELECT, ORDER BY, grouping, or a report-style query string.
Can FILTER pull data from another sheet?
Yes. Prefix the data range and condition ranges with the sheet name, such as 'Sheet 2'!A2:D100 and 'Sheet 2'!B2:B100.
Are these formulas generated with AI?
No. The builder uses deterministic TypeScript functions in the browser and does not call any AI API.
Do I need to upload my spreadsheet?
No. Enter ranges and criteria manually. The site does not upload, store, or process spreadsheet files.
Can I copy the generated formula?
Yes. Each output includes a copy button so you can paste the formula into Excel or Google Sheets.