FILTER function in Excel with multiple criteria: AND, OR and both

The FILTER syntax

=FILTER(array, include, [if_empty])

• array: the rows you want back, one column or the whole table.
• include: an array of TRUE/FALSE with the same number of rows, usually a comparison such as B2:B11="East".
• if_empty (optional): what to show when no row matches. Without it, FILTER returns #CALC!.

The result is a dynamic array: you type the formula in one cell and Excel spills the matching rows into the cells below and to the right. FILTER is available in Microsoft 365, Excel 2021 and later and Excel for the web. Put the criteria in cells (E13 to E15 in the example) instead of typing them into the formula, so the extract updates when someone changes a criterion.

AND: multiply the conditions

For orders from the East region and with an amount of at least 1,000:

=FILTER(A2:E11,(B2:B11=E13)*(E2:E11>=E14),"No orders")

Each condition returns ten TRUE/FALSE values. Multiplying them turns TRUE into 1 and FALSE into 0, and a row only keeps a 1 if both conditions are true, because 1 × 1 = 1 while anything times 0 is 0. FILTER keeps the rows with a 1: SO-1001 (1,240), SO-1003 (2,150) and SO-1008 (3,050), a total of 6,440. Add a third condition with another *( … ). The brackets around each condition are required, because without them Excel multiplies before it compares.

Worked example: ten orders, one criteria block, FILTER with AND and OR

Rows 2 to 11 are the data and E13 to E15 the criteria. Change a criterion in your own copy and every result updates. Hover over a green cell to see its formula.

Worked example: ten orders, one criteria block, FILTER with AND and OR

	A	B	C	D	E
1	Order	Region	Channel	Rep	Amount
2	SO-1001	East	Online	Kim	1,240
3	SO-1002	West	Retail	Diaz	860
4	SO-1003	East	Retail	Kim	2,150
5	SO-1004	North	Online	Patel	640
6	SO-1005	East	Online	Lee	480
7	SO-1006	West	Online	Diaz	1,730
8	SO-1007	South	Retail	Patel	1,120
9	SO-1008	East	Online	Lee	3,050
10	SO-1009	North	Retail	Kim	920
11	SO-1010	West	Online	Diaz	1,380
12
13	Criteria: region				East
14	Criteria: minimum amount				1,000
15	Criteria: channel				Online
16
17	AND: region = E13 and amount ≥ E14 (the spilled orders)				Result
18	1st matching order				SO-1001
19	2nd matching order				SO-1003
20	3rd matching order				SO-1008
21	Number of matches				3
22	Total amount of the matches				6,440
23	Largest match first: order at the top of SORT(FILTER())				SO-1008
24	OR and combined criteria				Result
25	OR: region = E13 or channel = E15, number of orders				7
26	OR: total amount				10,670
27	(East or West) and Online, number of orders				5
28	(East or West) and Online, total amount				7,880
29	Amount between 500 and 1,500, number of orders				6
30	No match: region Central, with an if_empty message				No orders

Fictional orders. In Excel, a single =FILTER(…) in E18 spills all matches at once; the grid uses INDEX(FILTER(…),k) so each spilled value can be shown and checked in its own cell.

OR: add the conditions

For orders that are from the East or sold online:

=FILTER(A2:E11,(B2:B11=E13)+(C2:C11=E15))

Adding the two arrays gives 0 when neither condition is true, 1 when one is and 2 when both are. FILTER treats every number other than 0 as TRUE, so an online order from the East (2) appears once, not twice. The result is 7 orders with a total of 10,670.

AND and OR together

Online orders from the East or the West need an OR group inside an AND:

=FILTER(A2:E11,((B2:B11="East")+(B2:B11="West"))*(C2:C11=E15))

The extra pair of brackets around the OR group decides the order of the calculation, exactly like (a + b) × c in arithmetic. The formula returns 5 orders worth 7,880. With a longer list of allowed values, keep them in a range and test membership instead: ISNUMBER(MATCH(B2:B11,G2:G5,0))*(C2:C11=E15). A range of values works the same way, two conditions on one column multiplied: (E2:E11>=500)*(E2:E11<=1500) finds the 6 orders between 500 and 1,500.

Why AND() and OR() do not work inside FILTER

=FILTER(A2:E11,AND(B2:B11="East",E2:E11>=1000)) looks logical but fails. AND and OR collapse a whole array into one single TRUE or FALSE, so FILTER no longer gets one answer per row and returns an error. The same is true in SUMPRODUCT and other array formulas, which is why * and + are the standard way to write multiple criteria.

No match, sorting, counting and totals

• No match: there are no orders from the Central region yet, so =FILTER(A2:A11,B2:B11="Central","No orders") shows No orders instead of #CALC!. Use "" for an empty cell.
• Sorted: wrap the result in SORT. =SORT(FILTER(A2:E11,(B2:B11=E13)*(E2:E11>=E14)),5,-1) sorts the matches by column 5, the amount, largest first, so SO-1008 comes to the top.
• Count and total: =ROWS(FILTER(…)) counts the matching rows and =SUM(FILTER(E2:E11,…)) totals them. For a total alone, SUMIFS is shorter, but the FILTER version also works for OR logic.

How the example shows the spilled result

In Excel you would type =FILTER(A2:A11,(B2:B11=E13)*(E2:E11>=E14)) once in E18 and the three order numbers would spill into E18:E20. The example grid on this page shows every value in its own checked cell, so E18 to E20 use =INDEX(FILTER(…),k), which picks the k-th row of the same spilled result. The values are identical. If you need a version for Excel 2019 or older, which has no FILTER, a k-th match helper with INDEX and a running COUNTIF does the same job, as in the guide on INDEX MATCH with multiple criteria.

Common mistakes

• Missing brackets around each condition. B2:B11=E13*E2:E11>=E14 multiplies first and compares later. Write (B2:B11=E13)*(E2:E11>=E14).
• Using AND() or OR(). They return one single value for the whole range, so FILTER fails. Use * for AND and + for OR.
• Ranges of different heights. The include array must have exactly as many rows as the array, otherwise FILTER returns #VALUE!.
• Cells in the way of the spill. Anything typed below or beside the formula blocks the result and shows #SPILL!. Leave room for the largest possible result.
• No if_empty argument. When nothing matches, FILTER returns #CALC!, which breaks totals that refer to it. Add a message such as "No orders".
• Text that only looks the same. "East " with a trailing space or amounts stored as text do not match. Clean the data with TRIM and VALUE first.