Excel FILTER function with multiple criteria: AND, OR, mixed

The moment a workbook grows past a hundred rows, hand-filtering starts costing you. AutoFilter forgets the setting the next time you open the file, and if the underlying range grows, your view goes stale silently. The Excel FILTER function fixes both problems: it returns a live subset of your data that updates the instant the source changes, and once the criteria syntax clicks, you rarely reach for AutoFilter again.

This guide walks the criteria patterns you actually use in real work — AND, OR, mixed logic, and the follow-on chains with SORT and UNIQUE — plus the two error paths that trip up almost every first attempt.

Where FILTER earns its place

FILTER is a dynamic array function. Type it into a single cell and Excel spills the result across as many rows and columns as the match needs. Change a value in the source and the spilled result recalculates on the spot. AutoFilter cannot do that — it hides rows in place, and hidden rows stay hidden even after the criterion no longer applies.

The second reason to prefer FILTER is composition. You can feed its output straight into SORT, UNIQUE, or CHOOSECOLS without a single copy-paste. That gives you a one-formula reporting layer that survives new rows, deleted rows, and column reorderings.

Syntax and one criterion

The function takes three arguments — one you would expect, and two that catch people out on the first try.

=FILTER(array, include, [if_empty])

array is the range you want rows from. include is a boolean array the same height (or width) as array: TRUE keeps the row, FALSE drops it. if_empty is the value returned when nothing matches — a string, a number, or another formula.

The single-criterion form is what most people see first. Suppose sales sit in B2:D200, with the region in column C, and you want only the East rows:

=FILTER(B2:D200, C2:C200="East", "No matches")

The boolean condition C2:C200="East" evaluates on every row, and the resulting TRUE/FALSE array is what selects the rows to keep. The literal string "No matches" shows up in the top-left cell if nothing qualifies — that is the guard against the #CALC! error we cover below.

Two criteria with AND: use the asterisk

Excel does not take a boolean AND directly inside FILTER. It takes an array of TRUEs and FALSEs, and to combine two arrays under AND logic you multiply them. TRUE times TRUE is 1, anything else is 0.

=FILTER(B2:D200, (C2:C200="East")*(D2:D200>500), "No matches")

The two boolean arrays multiply element by element. A row only survives when both conditions are TRUE — that is the whole mechanism. Wrap each condition in its own parentheses; leaving them off leads to bracket-precedence bugs the moment you add a third criterion.

Here is what the layout looks like on the sheet:

The formula returns rows 2 and 5 only — Ana’s East order at 612 and Cy’s East order at 905. Row 3 misses on region; row 4 misses on amount. Two masks, one survivor set.

OR logic with the plus sign

To combine conditions under OR, add the boolean arrays instead of multiplying. Any element that lands at 1 or higher counts as TRUE.

=FILTER(B2:D200, (C2:C200="East")+(C2:C200="West"), "No matches")

This returns every row where the region is East or West. If a row somehow matched both — impossible here since a cell holds one value, but relevant in more elaborate checks — the sum would be 2, still truthy, still kept.

The mental model is worth pausing on. FILTER does not evaluate logical AND or OR directly; it does arithmetic on boolean arrays. Multiplication is AND. Addition is OR. Once that clicks, mixing them makes sense.

Mixing AND and OR: parentheses matter

Real filters are almost never pure AND or pure OR. You want East or West, and amount above 500, or top rep, and region East or North. This is where the syntax stops being obvious, and where most tutorials stop covering it.

Wrap each logical group in its own parentheses, then combine them:

=FILTER(B2:D200,
  ((C2:C200="East")+(C2:C200="West"))*(D2:D200>500),
  "No matches")

Read the formula from the inside out. The two region checks add together to form an “East or West” mask. The amount check forms its own mask. The outer multiplication says: keep rows where the region mask is truthy and the amount mask is truthy.

=FILTER(B2:D200,
  (C2:C200="East")+(C2:C200="West")*(D2:D200>500))

=FILTER(B2:D200,
  ((C2:C200="East")+(C2:C200="West"))*(D2:D200>500))

The Before version relies on arithmetic precedence: multiplication happens before addition, so the amount filter only applies to the West rows. Every East row survives regardless of amount — almost always wrong.

Once you go past three conditions, a stacked layout makes the grouping easy to see and easier to audit. The Excel LET function lets you name each mask before the final combination, which is worth the extra line as soon as the formula stops fitting on one row.

Handle empty results and the #CALC! error

When no row matches and you omit the third argument, FILTER returns #CALC!. This is not a bug — Excel refuses to spill an empty array. The fix is always the same: supply an if_empty value.

=FILTER(A2:C100, B2:B100="Overdue", "No overdue accounts")

Use a short human sentence, an em dash, an empty string, or a numeric fallback like 0 — pick whichever matches what downstream cells expect to read. Skipping this argument is the single most common cause of dashboard blowups when a filter suddenly matches nothing.

• ✓ Every criterion range is the same size as the source array
• ✓ Each condition is wrapped in its own set of parentheses
• ✓ AND uses *, OR uses +
• ✓ An if_empty value is always present
• ✓ The spill area below the formula is empty

The last item is the second common failure. FILTER spills downward and to the right. If a cell inside the spill area already holds a value, Excel returns #SPILL! instead of your result. Clear the block first — a related class of spill issues shows up on the aggregation side, covered in the SUMPRODUCT multi-criteria guide.

Chain FILTER with SORT, UNIQUE, and CHOOSECOLS

The real payoff of dynamic arrays is composition. Wrap the FILTER call in another function; the whole chain still spills from one cell.

=SORT(
  UNIQUE(
    CHOOSECOLS(
      FILTER(A2:E1000, (C2:C1000="East")*(D2:D1000>500)),
      2, 4)),
  2, -1)

Reading inside out: FILTER pulls the East rows above 500, CHOOSECOLS keeps only columns 2 and 4 (rep and amount), UNIQUE removes duplicate rep-amount pairs, and SORT orders the result by column 2 (amount) in descending order. One cell drives the whole pipeline, and one edit to any source value refreshes it.

1. Start with the raw FILTER call and confirm the rows are correct.
2. Wrap in CHOOSECOLS to drop columns you do not need in the report.
3. Wrap in UNIQUE if the report should collapse repeats.
4. Wrap in SORT last, so ordering is not fighting the deduplication step.

Sheets users get the same behavior from a slightly different function set — the shape of the pipeline is identical, and the same chaining pattern applies to the Google Sheets FILTER function.

What to reach for first

Start with one condition and the if_empty argument. Once that reads correctly, add the second condition using *. Mix AND and OR only after you have triple-checked the parenthesization, and lean on LET for anything with four or more masks. Everything else — sorting, deduplication, column selection — is a wrapper around a formula that already works.