FILTER with multiple criteria

Inside the formula

Example data

How this FILTER formula works

The combined mask is true for the Widget West and Gadget West rows. FILTER returns those two complete rows as a 2-by-3 dynamic array.

The top row above is the whole formula. Open any argument that holds a calculation of its own to see the values it used. Excel works out those inner parts first, because the function around them needs their results. Edit a cell in the grid to see every value update.

Try your own formula and data

The sample spreadsheet is fully editable. Change the formula or any cell it references. The intermediate values recalculate in your browser.

Practice with an exercise

Here is an exercise that uses FILTER. Type the formula into a real spreadsheet and we will check your answer as you go.

FILTER with multiple conditions

Intermediate

Build a weekly pipeline follow-up report by filtering sales rows that meet multiple criteria.

Open exercise

Common mistakes with FILTER

If this formula is not doing what you expect, one of these is usually why.

Why does FILTER return #SPILL!?

The cells where the dynamic array needs to appear are not empty. Clear the blocking values or move the formula to an area with enough open rows and columns.

Why does FILTER return #CALC!?

No rows matched and the optional if_empty argument was omitted. Add a third argument such as "No matches" when an empty result is expected.

How do I use OR conditions in FILTER?

Add Boolean tests instead of multiplying them. Addition keeps a row when either condition is TRUE; multiplication requires both.

Why are my FILTER criteria different sizes?

Each include range must align with the rows in the source array. Here B2:B8 and C2:C8 both correspond exactly to A2:C8.