Spreadsheets guide
Google Sheets FILTER Formula Builder Guide
This page holds the detailed reference that supports the focused interactive tool.
Open the Google Sheets FILTER Formula BuilderFILTER with mixed AND and OR logic
Use mixed logic when one condition is required and either of two other conditions may match. Keep the OR choices inside one pair of parentheses before combining them with the required condition.
In the sample data, this formula returns the Paid Widget row for 420 and the Paid Gadget row for 275.
=FILTER(A2:D100,(B2:B100="Paid")*((D2:D100="Widget")+(D2:D100="Gadget")))Sort FILTER results
Wrap FILTER in SORT when the matching rows should be ordered before they spill into the sheet. The sort column number is counted within the returned range, starting with 1.
In the sample data, the formula below removes the blank-status row and returns the remaining amounts in this order: 420, 310, 275.
=SORT(FILTER(A2:D100,B2:B100<>""),3,FALSE)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. Wrap it in SORT for one straightforward result order. Use QUERY when you need to select or reorder columns, group and aggregate rows, or manage several report clauses 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. |
| (B2:B100="Paid")*((D2:D100="Widget")+(D2:D100="Gadget")) | Returns the Paid Widget row for 420 and the Paid Gadget row for 275. | One rule is required and either of two other values may match. |
| SORT(FILTER(...),3,FALSE) | Returns the nonblank-status rows with amounts ordered 420, 310, then 275. | The filtered result should be ordered by its third column. |
| 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. |
| Mixed AND and OR | =FILTER(A2:D100,(B2:B100="Paid")*((D2:D100="Widget")+(D2:D100="Gadget"))) | Require one condition while allowing either of two other values. |
| Filter, then sort | =SORT(FILTER(A2:D100,B2:B100<>""),3,FALSE) | Return matching rows and order the third returned column from highest to lowest. |
| 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. |
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. |
| Mixed logic is not generated | Condition 1 AND (condition 2 OR condition 3) needs three active conditions. | Use Add condition until all three condition groups are visible and complete. |
| SORT uses the wrong column | The column number was counted from the source sheet instead of from the returned range. | Count from the left edge of the FILTER result. In A2:D100, Amount is returned column 3. |
| 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).
- Mixed logic needs three active conditions so the required rule and two OR choices are unambiguous.
- SORT column numbers are counted inside the returned range, not from the worksheet column letters.
- 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 combine AND and OR in one FILTER formula?
Keep the OR choices inside parentheses, then multiply that group by the required condition. For example, FILTER(A2:D100,(B2:B100="Paid")*((D2:D100="Widget")+(D2:D100="Gadget"))).
How do I sort FILTER results in Google Sheets?
Wrap FILTER in SORT and use the returned column number, such as SORT(FILTER(A2:D100,B2:B100<>""),3,FALSE) to sort the third returned column from highest to lowest.
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 and one straightforward sort. QUERY is better when you need selected columns, grouping, aggregation, or several report clauses.
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 built-in spreadsheet rules in your browser and does not call an AI service.
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.