The FILTER guide
=FILTER(A2:C15, (D2:D15=H1)*(E2:E15="Open"), "None") returns every row of A2:C15 where both conditions hold, in the order they appear, and spills them into the cells below. It works in Excel 2021, Excel for Microsoft 365 and Excel for the web.
The syntax
=FILTER(array, include, [if_empty])
- array: the rows you want back, usually several columns, like A2:C15.
- include: one TRUE or FALSE per row, from a comparison on a whole column:
D2:D15=H1. It must be as tall as the array. - if_empty: what to show when no row matches, like
"None".
Like UNIQUE, FILTER spills: write it in one cell and the result fills the cells below and to the right. If one of them isn't empty, you get #SPILL!. See UNIQUE and #SPILL!.
Two conditions: * and +
Multiply two conditions for AND: (D2:D15=H1)*(E2:E15="Open") is 1 only on rows where both are TRUE. Add them for OR: (D2:D15="North")+(D2:D15="South") keeps a row when either is TRUE. Put each condition in brackets.
The functions AND() and OR() don't work here. They boil the whole range down to one TRUE or FALSE, so FILTER gets a single value instead of one per row and returns #VALUE!.
#CALC! and #VALUE!
| Error | Cause | Fix |
|---|---|---|
#CALC! | No row matched, and there's no third argument. Excel can't show an empty list. | Add if_empty: "None" or "". |
#VALUE! | include isn't as tall as the array (D2:D14 against A2:C15), or AND() inside include. | Use the same rows everywhere; use * and +. |
#SPILL! | Something is in the cells the result needs. | Clear them. |
Put the criterion in a cell
Compare with a cell, D2:D15=H1, instead of typing "North" into the formula. Change H1 and the list follows. That's also why if_empty matters: the formula works for North today and has to keep working for a region with nothing open.
The comparison ignores capitals, so "north" matches "North". It doesn't ignore spaces: "North " with a space at the end won't match. Clean the column with TRIM first.
Common mistakes
- One condition when the task has two. Closed orders from the right region come through.
- + instead of *. Every open order comes through, from every region.
- Only one column as the array. You get order numbers without the customer and amount. Give FILTER all the columns you want back.
- No if_empty. Fine until the day nothing matches, then #CALC!.
To sort the result, wrap it: =SORT(FILTER(…), 3, -1). See SORT and UNIQUE. To add the matches up instead of listing them, SUMIFS is simpler.
FAQ
- How do I filter with two conditions in Excel?
- Multiply the conditions inside FILTER: =FILTER(A2:C15,(D2:D15="North")*(E2:E15="Open")). Each condition goes in brackets. For either condition, add them with + instead.
- Why does FILTER return #CALC!?
- No row matched and the formula has no third argument. Add one, such as =FILTER(A2:C15,D2:D15=H1,"None"), and that text shows instead.
- Why does FILTER return #VALUE!?
- The include range isn't the same height as the array, or AND() or OR() was used inside it. Make every range cover the same rows and use * for AND, + for OR.
- Which versions of Excel have FILTER?
- Excel 2021, Excel 2024, Excel for Microsoft 365 and Excel for the web. In older versions, use an AutoFilter or SUMIFS and COUNTIFS for totals.



































































