Runs this calculation on the values.
- Left side
(B2:B8="West")- Right side
(C2:C8>100)
FILTER with multiple criteria
FILTER keeps every row where Region is West and Price is greater than 100. Multiplying the conditions produces one include mask that the formula turns into a smaller table.
How Excel works this out
Inner to outerPull specific rows from a range based on conditions you define, returning a dynamic array of matching results.
- Array
A2:C8The range or array you want to filter.- Include
(B2:B8="West")*(C2:C8>100)A Boolean array the same height (or width) as the array. Rows where this evaluates to TRUE are included in the results.- Source shape
A2:C87 rows x 3 columns
Formula sample 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 rows above are ordered from the innermost calculation to the final result. Excel has to finish an inner function before the function around it can use that value. Edit a cell in the grid to see that dependency chain update for real.
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
IntermediateBuild a weekly pipeline follow-up report by filtering sales rows that meet multiple criteria.
Open exerciseCommon 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.