The FILTER Function in Excel (With AND and OR)

The Excel FILTER function returns every row that meets your conditions, as a live copy that updates when the data changes. You can combine conditions with AND or OR, and show your own message when nothing matches. This guide is for anyone who needs "all East deals" or "every order over $10,000" in a report or an Excel test. FILTER needs Excel 2021, Excel 2024 or Microsoft 365.

What the Excel FILTER function does#

FILTER checks each row of a range against a test and returns the rows that pass.

=FILTER(array, include, [if_empty])

• array is the data to return, without its header row, such as A2:C9.
• include is the test, checked row by row, such as B2:B9="East". It must cover the same rows as the array.
• if_empty is optional: what to show when no row passes, such as "None".

FILTER is a dynamic array function. You type it in one cell, and the matching rows spill into the cells below and to the right. Those cells must be empty, or you get #SPILL!; see our guide to the #SPILL! error. The FILTER function guide has a quick reference.

How to filter by one condition#

Here is a list of sales deals:

To get every East deal, type this in E2:

=FILTER(A2:C9,B2:B9="East")

Four rows spill into E2:G5: Ava Thompson, Noah Patel, Olivia Brooks and Sophia Martin, each with their region and amount. Change a region in column B and the result updates at once.

The test can point at a cell instead of typed text. If L1 holds West, then =FILTER(A2:C9,B2:B9=L1) returns Liam Garcia and Ethan Nguyen. Change L1 and you have a simple, reusable report.

FILTER doesn't copy the headers. To label the result, type them yourself above it, or put =A1:C1 in E1 so the header row spills across and stays in step with the source. If new deals will be added below row 9, make the ranges longer, such as A2:C100 and B2:B100: empty rows fail the test, so they don't show up.

How to filter with AND (two conditions that must both be true)#

To get East deals of 10,000 or more, put each test in brackets and multiply them:

=FILTER(A2:C9,(B2:B9="East")*(C2:C9>=10000))

This returns Ava Thompson (12,500) and Olivia Brooks (10,000). Here is why it works: each test gives TRUE or FALSE for every row, and Excel treats TRUE as 1 and FALSE as 0. A row gives 1 only when both tests are 1. Noah Patel is East but 6,400, so his row gives 1 × 0 = 0 and is left out.

The same trick gives you a range. To get deals from 8,000 to 12,500, test the same column twice:

=FILTER(A2:C9,(C2:C9>=8000)*(C2:C9<=12500))

This returns Ava Thompson, Liam Garcia, Olivia Brooks and Mason Clark. Dates work the same way, because Excel stores dates as numbers.

Don't use the AND function here. AND(B2:B9="East",C2:C9>=10000) returns a single TRUE or FALSE for the whole range, not one per row, so FILTER returns #VALUE!.

How to filter with OR (either condition can be true)#

To get every deal from West or South, add the tests instead:

=FILTER(A2:C9,(B2:B9="West")+(B2:B9="South"))

This returns Liam Garcia, Emma Wilson, Ethan Nguyen and Mason Clark. A row passes when the sum is 1 or more, which means at least one test is TRUE. You can combine both ideas, too: ((B2:B9="West")+(B2:B9="South"))*(C2:C9>10000) keeps West or South deals over 10,000: Emma Wilson and Ethan Nguyen. The extra brackets around the OR part matter, because Excel multiplies before it adds.

How to show a message when nothing matches#

If no row passes, FILTER returns #CALC!, Excel's way of saying the result is empty. The third argument replaces it:

=FILTER(A2:C9,C2:C9>25000,"None")

No deal is over 25,000, so the cell shows None. Use "" instead if you want the cell to look empty.

How to use FILTER with other functions#

FILTER's result can go straight into another function.

• Count the matches: =ROWS(FILTER(A2:C9,B2:B9="East")) returns 4. Use ROWS, not COUNTA: COUNTA would count every cell, three per row.
• Add up the matches: =SUM(FILTER(C2:C9,B2:B9="East")) returns 33,700. SUMIFS gives the same total, but this pattern works with any test.
• Return one column: =FILTER(A2:A9,C2:C9>10000) returns just the names Ava Thompson, Emma Wilson, Ethan Nguyen. Olivia's 10,000 isn't more than 10,000, so she is left out.
• Sort the result: =SORT(FILTER(A2:C9,B2:B9="East"),3,-1) lists the East deals from largest to smallest. Our guide to SORT and SORTBY explains the extra arguments.
• Remove repeats: wrap FILTER in UNIQUE to list each value once; see the UNIQUE function.
• Match part of the text: =FILTER(A2:C9,ISNUMBER(SEARCH("son",A2:A9))) returns the reps whose names contain "son". That gives Ava Thompson and Emma Wilson, but also Mason Clark, so check short search words carefully.

These combinations are everyday tasks in analyst roles; see the Excel skills every data analyst needs.

Common problems with the FILTER function#

Most FILTER errors fall into a few groups:

1. #SPILL!: the cells where the result should go aren't empty. Clear them or move the formula.
2. #VALUE!: the array and the test have different numbers of rows, such as A2:C9 with B2:B10. Make them match.
3. #VALUE! from AND or OR. Use * for AND and + for OR instead.
4. #CALC!: nothing matched. Add an if_empty message, and check the test for typos or extra spaces.
5. Including the header. A1:C9 with B1:B9 works but treats the header as data. Start both ranges on row 2.
6. #NAME? in older Excel. FILTER doesn't exist in Excel 2019 or earlier. Use AutoFilter there; see FILTER function vs AutoFilter for when each fits.
7. Text criteria that don't match. FILTER's = test isn't case-sensitive, but "East " with a trailing space doesn't equal "East". Clean the data with TRIM first.

Practise it#

Try the exercise on this page: it needs two conditions at once, so you'll multiply the tests. Then practise FILTER in a live spreadsheet, with AND and OR conditions, an if_empty message and a count of the results.