Google Sheets FILTER with Multiple Conditions (AND, OR, Dates and Text)

FILTER returns the rows of a range that meet one or more conditions. One condition is easy. With several, you have to choose how to join them: all of them must be true (AND), or any of them (OR). FILTER has no AND or OR option of its own, and the AND() and OR() functions don’t work inside it. You join conditions with extra arguments, multiplication or addition.

We tested every formula below on a small orders table. To make it realistic, it has a few problems on purpose: an empty row, a region typed in lower case, one with a trailing space, an amount stored as text and a date with a time.

Tested in Google Sheets. We ran these formulas on November 17, 2026 and checked which rows each one returned. How we test

The sample data

Columns A to F, header in row 1, data in rows 2 to 15:

Row Date (A) Region (B) Rep (C) Product (D) Status (E) Amount (F)
2 Jan 5 North Ana Apple Paid 120
3 Jan 12 South Ben Banana Pending 80
4 Jan 20 North Cara Cherry Paid 200
5 Feb 2 East Ana Apple Paid 150
6 Feb 14 north Dev Banana Refunded 60
7 Feb 28 West Ben Cherry Paid 310
8 (empty row)
9 Mar 3 “North ” (trailing space) Cara Apple Pending 90
10 Mar 15 South Ana Grape Paid “250” (text)
11 Mar 31, 18:30 East Dev Apple Paid 75
12 Apr 10 West Cara Banana Paid 180
13 Apr 22 South Ben Apple Refunded 40
14 May 1 North Ana Kiwi Paid 220
15 May 9 East Ben Grape (blank) 130

All dates are in 2026. In the results below we list the row numbers each formula returned, because that is the easiest way to compare them.

AND: every condition must be true

The simplest way is to add each condition as another argument. FILTER keeps a row only when all of them are true:

=FILTER(A2:F15, B2:B15="North", E2:E15="Paid")

Result: rows 2, 4 and 14, the paid North orders. You can add as many conditions as you need. Adding F2:F15>=150 as a third one left rows 4 and 14.

Multiplying the conditions inside one argument gives exactly the same result:

=FILTER(A2:F15, (B2:B15="North")*(E2:E15="Paid"))

Each condition becomes 1 (true) or 0 (false) for every row, and the product is 1 only when all of them are 1. Separate arguments are easier to read. Multiplication is useful when you need to combine AND with OR, as shown further down.

OR: any condition can be true

For OR, add the conditions instead of multiplying them, inside a single argument:

=FILTER(A2:F15, (B2:B15="North")+(B2:B15="South"))

Result: rows 2, 3, 4, 6, 10, 13 and 14. The sum is 0 only when every condition is false. Any other number keeps the row.

The conditions can also come from different columns. Orders from the East region or by Ana:

=FILTER(A2:F15, (B2:B15="East")+(C2:C15="Ana"))

Result: rows 2, 5, 10, 11, 14 and 15. Row 5 matches both conditions (East and Ana), so its sum is 2, but it still appears only once.

Found in testing: the OR formula returned row 6, where the region is “north” in lower case, because = ignores upper and lower case. It did not return row 9, “North ” with a trailing space: to Sheets that is a different value. The same happened in the AND examples. Before trusting a filter, check the data for extra spaces. Our guide to removing extra spaces shows how to find and remove them.

OR with many values in one column

With a long list of values, adding one condition per value gets unwieldy. Two shorter options:

=FILTER(A2:F15, ISNUMBER(MATCH(B2:B15, {"North";"South"}, 0)))
=FILTER(A2:F15, REGEXMATCH(B2:B15, "^(North|South)$"))

MATCH returned the same 7 rows as the addition: 2, 3, 4, 6, 10, 13 and 14. REGEXMATCH returned 6 rows, without row 6. Unlike = and MATCH, REGEXMATCH distinguishes upper and lower case, so “north” didn’t match. You can also replace {"North";"South"} with a range that holds the list: with the two regions typed in two cells, MATCH returned the same 7 rows.

AND and OR together

Paid orders from the North or the South: add the regions together, wrap the sum in parentheses and multiply it by the status:

=FILTER(A2:F15, ((B2:B15="North")+(B2:B15="South"))*(E2:E15="Paid"))

Result: rows 2, 4, 10 and 14. The parentheses around the sum matter, because multiplication is evaluated before addition. Without them, the formula means “North, or South and paid”: it returned rows 2, 4, 6, 10 and 14, including row 6, a refunded North order.

Dates between two dates

Use one condition for the start and one for the end:

=FILTER(A2:F15, A2:A15>=DATE(2026,2,1), A2:A15<DATE(2026,4,1))

Result: rows 5, 6, 7, 9, 10 and 11, every order from February and March. Write the dates with DATE. In our test, comparing with a date typed as text, A2:A15>="2026-02-01", found no rows at all.

Found in testing: the “obvious” version with <=DATE(2026,3,31) returned only rows 5, 6, 7, 9 and 10. It silently dropped row 11, because that order has a time (March 31 at 18:30). A date with a time is later than midnight on March 31. Use < the day after the end date, as in the formula above, and every order from the last day is included. The same trap affects SUMIFS, as our SUMIFS between two dates guide shows.

Text that contains a word

FILTER doesn’t accept wildcards like *: D2:D15="*an*" found no rows at all. To match part of the text, use SEARCH, which also ignores case:

=FILTER(A2:F15, ISNUMBER(SEARCH("an", D2:D15)))

Result: rows 3, 6 and 12, the three Banana orders. To exclude rows that contain the text, wrap the condition in NOT:

=FILTER(A2:F15, NOT(ISNUMBER(SEARCH("an", D2:D15))))

That returned 11 rows, and one of them was row 8, the empty row: an empty cell doesn’t contain “an” either. The same happened with E2:E15<>"Refunded", which returned the empty row 8 and row 15, where the status is blank. If your range has empty rows, add a condition such as A2:A15<>"".

A “contains” search also catches values with extra spaces: ISNUMBER(SEARCH("North", B2:B15)) returned rows 2, 4, 6, 9 and 14, including “North “.

Sorting the result

Wrap FILTER in SORT. This returns the North orders with the largest amount first (column 6 of the result, descending):

=SORT(FILTER(A2:F15, B2:B15="North"), 6, FALSE)

The amounts came out as 220, 200, 120 and 60. For more complex sorting and grouping, QUERY is often simpler.

Numbers stored as text

Row 10 has the amount “250” stored as text. Numeric conditions handle it badly:

  • F2:F15>300 returned rows 7 and 10. In Sheets comparisons, text counts as greater than any number, so “250” passes “>300”.
  • F2:F15=250 found nothing, and FILTER returned #N/A.
  • SUM(FILTER(F2:F15, B2:B15="South")) gave 120 instead of 370, because SUM ignores the text.

Converting inside the condition fixed the comparison: VALUE(F2:F15)>200 returned rows 7, 10 and 14. A better long-term fix is to convert the column to numbers.

Exact case and trailing spaces

  • To match upper and lower case exactly, use EXACT: EXACT(B2:B15, "North") returned rows 2, 4 and 14, without “north” or “North “.
  • To ignore extra spaces, compare the trimmed text: TRIM(B2:B15)="North" returned rows 2, 4, 6, 9 and 14.

When no row matches

If no row meets the conditions, FILTER returns #N/A with the message “No matches are found in FILTER evaluation.” To show your own text instead, wrap it in IFERROR:

=IFERROR(FILTER(A2:A15, B2:B15="Central"), "No orders")

Keep in mind that IFERROR also hides every other error. If FILTER returns an error you don’t expect, our FILTER not working guide lists the causes we reproduced.

Quick reference

You want Condition
A and B cond1, cond2 or (cond1)*(cond2)
A or B (cond1)+(cond2)
Any value from a list ISNUMBER(MATCH(range, list, 0))
(A or B) and C ((cond1)+(cond2))*(cond3)
Between two dates range>=start, range<end+1
Contains text ISNUMBER(SEARCH("text", range))
Exact case EXACT(range, "Text")

If you only need to count or add up the matching rows rather than list them, see COUNTIFS with multiple criteria and SUMIFS with multiple criteria. To return a single value instead of whole rows, see XLOOKUP with multiple criteria.