Excel guide · 3 min read

How to filter with multiple criteria in Excel

Q: How do I filter by multiple values in one column?

Add one condition per value: =FILTER(A2:E9,(D2:D9="Laptop")+(D2:D9="Monitor")+(D2:D9="Dock")). For a long list kept in cells, use ISNUMBER(MATCH(D2:D9,K2:K5,0)) as the condition.

Quick answerPut each condition in brackets and multiply them for AND: =FILTER(A2:E9,(C2:C9="East")*(E2:E9>1000)). Add them for OR: =FILTER(A2:E9,(D2:D9="Laptop")+(D2:D9="Monitor")). FILTER is in Excel 365 and Excel 2021 and later; in older versions use AutoFilter or Advanced Filter.

Sample data

Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.

	A	B	C	D	E	G	H
1	Order	Rep	Region	Product	Amount	Region	East
2	A-2201	Maya	East	Laptop	1450	Min amount	1000
3	A-2202	Luis	West	Monitor	320
4	A-2203	Maya	East	Monitor	640
5	A-2204	Priya	East	Dock	189
6	A-2205	Luis	West	Laptop	2900
7	A-2206	Priya	Central	Laptop	1380
8	A-2207	Maya	East	Dock	1120
9	A-2208	Luis	Central	Monitor	960

AND: every condition must be true (multiply)

Each comparison returns a list of TRUE and FALSE, one per row. Multiplying two lists turns them into 1s and 0s, and a row only gets a 1 when both are true. FILTER keeps the rows with a 1.

Wrap every condition in its own brackets. Without them, Excel multiplies before it compares and the logic falls apart.

=FILTER(A2:E9,(C2:C9="East")*(E2:E9>1000))

Result: spills 2 rows × 5 columns

A-2201	Maya	East	Laptop	1450
A-2207	Maya	East	Dock	1120

2 rows: A-2201 (Maya, Laptop, 1,450) and A-2207 (Maya, Dock, 1,120).

OR: any condition can be true (add)

Adding the lists gives 1 when either is true and 2 when both are. FILTER treats any number other than 0 as keep, so 2 does no harm here.

Use OR across one column (Laptop or Monitor) or across different columns (East region or amount over 2,000).

=FILTER(A2:E9,(D2:D9="Laptop")+(D2:D9="Monitor"))

Result: spills 6 rows × 5 columns

A-2201	Maya	East	Laptop	1450
A-2202	Luis	West	Monitor	320
A-2203	Maya	East	Monitor	640
A-2205	Luis	West	Laptop	2900
A-2206	Priya	Central	Laptop	1380
A-2208	Luis	Central	Monitor	960

6 of the 8 orders: every Laptop and Monitor sale. The two Dock orders are left out.

AND and OR together

Bracket the OR part as one group, then multiply it by the AND condition. This reads as: East AND (Laptop OR Monitor).

=FILTER(A2:E9,(C2:C9="East")*((D2:D9="Laptop")+(D2:D9="Monitor")))

Result: spills 2 rows × 5 columns

A-2201	Maya	East	Laptop	1450
A-2203	Maya	East	Monitor	640

2 rows: A-2201 (Laptop, 1,450) and A-2203 (Monitor, 640).

A range of values, criteria cells and a no-match message

"Between" is two AND conditions on the same column. Point the conditions at input cells instead of typing values, so anyone can change H1 and H2 and the list updates. The third argument of FILTER is what to show when nothing matches; without it you get #CALC!.

=FILTER(A2:E9,(C2:C9=H1)*(E2:E9>=H2),"No orders")

Result: spills 2 rows × 5 columns

A-2201	Maya	East	Laptop	1450
A-2207	Maya	East	Dock	1120

Same 2 East orders of 1,000 or more. Change H1 to North and it shows No orders.

Return only some columns, sorted

FILTER returns whatever range you give as its first argument, so point it at just the columns you need, as long as they are next to each other. Wrap it in SORT to order the result.

=SORT(FILTER(B2:E9,(E2:E9>=500)*(E2:E9<=1500)),4,-1)

Result: spills 5 rows × 4 columns

Maya	East	Laptop	1450
Priya	Central	Laptop	1380
Maya	East	Dock	1120
Luis	Central	Monitor	960
Maya	East	Monitor	640

5 orders between 500 and 1,500, largest first: 1,450, 1,380, 1,120, 960 and 640, showing Rep, Region, Product and Amount.

Without formulas: AutoFilter and Advanced Filter

AutoFilter hides rows in place, and conditions in different columns combine with AND. For OR across different columns, use Advanced Filter: criteria typed on the same row are AND, criteria on separate rows are OR.

Click in the data and press Ctrl+Shift+L to add filter arrows.
Filter Region to East, then filter Amount with Number Filters > Greater Than Or Equal To > 1000.
For OR across columns: copy the headers to an empty area, type East under Region on one row and >2000 under Amount on the next row.
Go to Data > Advanced, set the List range and the Criteria range, and click OK.

Common mistakes

Using AND() or OR() inside FILTER

AND and OR return one single TRUE or FALSE for the whole range, not one per row, so FILTER fails. Use * for AND and + for OR.

Missing brackets around each condition

C2:C9="East"*E2:E9>1000 is read in the wrong order. Write (C2:C9="East")*(E2:E9>1000).

Ranges of different sizes

The include ranges must have the same number of rows as the data, or FILTER returns #VALUE!. Start and end every range on the same rows.

Counting with + and getting double counts

In FILTER, 2 is still keep. But if you reuse the OR logic in SUMPRODUCT to count rows, a row matching both conditions counts twice. Test the sum with >0 first.

FAQ

How do I filter by multiple values in one column?

Add one condition per value: =FILTER(A2:E9,(D2:D9="Laptop")+(D2:D9="Monitor")+(D2:D9="Dock")). For a long list kept in cells, use ISNUMBER(MATCH(D2:D9,K2:K5,0)) as the condition.

How do I filter rows that contain a word?

Use ISNUMBER(SEARCH("lap",D2:D9)) as the condition. SEARCH ignores case; use FIND to match case.

Why does FILTER show #CALC!?

No rows matched, and there is no third argument. Add one, for example "No orders" or "", to show that instead.

Can I filter with multiple criteria in Excel 2016?

Not with FILTER, which needs Excel 365 or 2021 and later. Use AutoFilter for AND across columns, or Advanced Filter for OR, which can also copy the result to another place.

Functions used

Related guides

Updated November 2, 2026