Use Excel’s FILTER function and combine criteria with multiplication for AND or addition for OR. With order data in A2:D7 (Order, Region, Rep, Sales), use =FILTER(A2:D7,(B2:B7="East")*(D2:D7>=500),"No matches") to return East-region orders with sales of at least 500; in the example below, the results are orders 1001 and 1004.
How FILTER works
FILTER returns rows or columns from a range when a corresponding include array evaluates to TRUE. Its basic syntax is =FILTER(array,include,[if_empty]): array is the data to return, include contains the test or tests that decide what stays, and the optional if_empty value appears when nothing matches.
For the examples in this article, the worksheet has headers in A1:D1 and these six records in A2:D7:
- 1001, East, Ana, 620
- 1002, West, Ben, 740
- 1003, East, Cara, 450
- 1004, East, Dan, 810
- 1005, North, Ana, 530
- 1006, West, Cara, 510
The formula returns matching rows in their original order and spills them into the cells below and to the right of the formula. You do not need to copy it down. FILTER is available in Microsoft 365 and Excel 2021 or later, including supported versions of Excel for Mac and Excel for the web. Older Excel versions may not recognize the function.
Combine criteria with AND
For rows that must satisfy every condition, multiply the logical tests with *. In the opening example, (B2:B7="East") checks the Region column, while (D2:D7>=500) checks Sales. Each test produces a TRUE/FALSE array. Multiplying them makes a row qualify only when both tests are TRUE; Excel treats TRUE as 1 and FALSE as 0 in this calculation.
With Region in B2:B7 and Sales in D2:D7, =FILTER(A2:D7,(B2:B7="East")*(D2:D7>=500),"No matches") returns two rows: order 1001 (East, Ana, 620) and order 1004 (East, Dan, 810). Order 1003 is in the East but does not meet the sales threshold, so it is excluded.
Add more AND conditions by multiplying another same-sized test. For example, with Rep names in C2:C7 as well, the following returns East orders with sales of at least 500 assigned to Ana:
With records in A2:D7, Region in B2:B7, Rep in C2:C7, and Sales in D2:D7, use =FILTER(A2:D7,(B2:B7="East")*(D2:D7>=500)*(C2:C7="Ana"),"No matches"). It returns order 1001 only. Parentheses around each test make the logic easier to read and help prevent mistakes when conditions become more involved.
Combine criteria with OR
For rows that may meet either of two conditions, add the logical tests with +. For example, to include records from either East or West, addition gives a nonzero result when at least one comparison is TRUE. The comparison >0 turns that result into a clear TRUE/FALSE include array.
With Region values in B2:B7 and the full records in A2:D7, use =FILTER(A2:D7,((B2:B7="East")+(B2:B7="West"))>0,"No matches"). It returns orders 1001, 1002, 1003, 1004, and 1006; the North-region order is left out. The parentheses mean “add the two tests, then check whether the total is greater than zero.”
OR can also join different kinds of tests. With Rep in C2:C7 and Sales in D2:D7, this example includes a row if its salesperson is Ana or its sales are at least 700:
With the records in A2:D7, use =FILTER(A2:D7,((C2:C7="Ana")+(D2:D7>=700))>0,"No matches"). The result is orders 1001, 1002, 1005, and 1006. Ana’s orders qualify even when sales are below 700, while other representatives qualify only when they meet the threshold.
Mix AND and OR conditions
For a rule such as “Region is East, and either Sales is at least 700 or Rep is Ana,” combine multiplication and addition. Multiplication joins the required East condition to a parenthesized OR group. Keeping the OR group together is essential: it ensures the East requirement applies to both alternatives.
With Region in B2:B7, Rep in C2:C7, Sales in D2:D7, and records in A2:D7, use =FILTER(A2:D7,(B2:B7="East")*(((D2:D7>=700)+(C2:C7="Ana"))>0),"No matches"). The result is orders 1001 and 1004. Both are East orders, and each also meets at least one condition in the grouped OR test.
A useful way to build a complicated include expression is to write the rule in plain language first, mark which conditions are “all required” and which are “any one is enough,” and then group the OR tests with parentheses. Test the logic on a small, known sample before relying on it for a large report.
Use worksheet cells for changing criteria
Hard-coded values such as “East” and 500 work for a one-off answer, but cells make a reusable report easier to operate. Put the selected region in H2 and the minimum sales value in H3. A user can then change either input without editing the formula.
With records in A2:D7, Region in B2:B7, Sales in D2:D7, the requested region in H2, and the minimum sales value in H3, enter =FILTER(A2:D7,(B2:B7=H2)*(D2:D7>=H3),"No matches"). If H2 contains East and H3 contains 500, it returns orders 1001 and 1004. You can apply the same idea to OR conditions by replacing a fixed criterion in a test with a cell reference.
For a growing source list, format the source data as an Excel table using Insert > Table, then use its column names in formulas. For a table named SalesData, with Region and Sales columns and criteria in H2 and H3, use =FILTER(SalesData,(SalesData[Region]=H2)*(SalesData[Sales]>=H3),"No matches"). Table references expand as rows are added. Put the formula outside the table: a spilled formula cannot spill within an Excel table, although the table can be the source.
Spill behavior and sorting results
The formula cell is the top-left corner of the returned array. Excel displays a colored outline around the spill range when you select the formula cell. Leave the cells where results need to appear empty; if any are occupied, Excel can show #SPILL!. Clear the obstructing cells or move the formula to an area with enough room. A spill range can also be referenced from another formula using the spill operator, such as F2# when the FILTER formula begins in F2.
FILTER preserves the source order; it does not automatically sort the matching rows. To sort the filtered results by Sales from largest to smallest, wrap the FILTER result in SORT. With data in A2:D7 and Sales in the fourth column of the returned array, use =SORT(FILTER(A2:D7,(B2:B7="East")*(D2:D7>=500),"No matches"),4,-1). The result is order 1004 followed by 1001, because 810 is greater than 620. SORT is available in Microsoft 365 and Excel 2021 or later.
Common errors and practical fixes
- No matches: If you omit the optional third argument and no records qualify, FILTER returns
#CALC!. Supply a message such as"No matches"as shown in the examples. This argument is one result for the empty case, not a replacement row for every missing match. - #SPILL!: Check whether cells in the destination area contain data, merged cells, or other obstacles. Move or clear the obstruction, and allow enough space for the result to grow.
- Wrong results from AND/OR: Multiplication means every condition must pass; addition with
>0means at least one must pass. Review parentheses where you combine the two types. - Different range sizes: The include tests must line up with the rows in the array. If the array covers A2:D100, a condition covering B2:B20 does not describe the same set of rows and can cause an error or incorrect logic. Keep the starting and ending rows aligned.
- Unexpected text or number matches: Check for extra spaces, inconsistent spelling, or numbers stored as text. For example, “East ” with a trailing space is not the same text as “East.” Clean or standardize source values when appropriate.
- Comma versus semicolon separators: Excel’s argument separator depends on regional settings. If the examples produce a formula-entry error, your installation may require semicolons instead of commas; the underlying function and logic are unchanged.
For text that contains a word rather than exactly matching a category, use a text-search test. With names or descriptions in C2:C7 and the records in A2:D7, =FILTER(A2:D7,ISNUMBER(SEARCH("an",C2:C7)),"No matches") returns rows where the text in column C contains “an,” without regard to capitalization. This is different from C2:C7="Ana", which looks for an exact match. SEARCH returns an error when text is absent, and ISNUMBER converts a successful search position to TRUE and an error to FALSE.
When to use FILTER instead of manual filtering
Use FILTER when the output should update automatically as source values or criteria change, or when a formula-driven report needs to feed other formulas. It is particularly useful for multiple conditions because the logic is visible in the formula and can be combined with cell-based inputs. The formula returns a live result rather than changing the source data.
Excel’s ribbon filters are convenient for a quick, interactive inspection: select the data and choose Data > Filter, then use the header dropdowns to narrow records. That approach is easy to operate manually, but it does not create a separate dynamic result array for another report area. Advanced Filter can copy matching records to another location for certain tasks, but it is a dialog-driven tool rather than a formula result that recalculates from criteria cells. Choose based on whether you need a temporary view or a reusable, updating output.
If FILTER is unavailable in the Excel version you must use, the built-in Data > Filter feature is a practical alternative for interactive filtering. For a formula-based report in an older version, alternatives may include helper columns with tests and traditional lookup or index formulas, but those require more setup and do not provide FILTER’s simple spill behavior. Sharing a workbook with people on older Excel can also affect whether they can calculate a FILTER formula; confirm the recipients’ versions before depending on it.
Frequently Asked Questions
How do I filter by multiple criteria in Excel with AND?
Put each condition in parentheses and multiply the tests inside FILTER’s include argument. For example, (B2:B7="East")*(D2:D7>=500) keeps only rows where both the region and sales conditions are true.
How do I use OR criteria in an Excel FILTER formula?
Add the condition arrays and test whether the total is greater than zero. A pattern such as ((B2:B7="East")+(B2:B7="West"))>0 includes a row that matches either region.
Can FILTER use criteria stored in cells?
Yes. Refer to the criteria cells in the include tests, such as B2:B7=H2 and D2:D7>=H3. Changing H2 or H3 recalculates the returned rows.
Why does my FILTER formula return #CALC!?
Usually no rows matched and the formula did not specify an if_empty result. Add a third argument such as "No matches". If you expected a match, also verify spelling, spaces, data types, and the alignment of the source and criteria ranges.
Can a FILTER formula go inside an Excel table?
No. Dynamic array results need to spill into worksheet cells, and a table does not allow a spill range inside it. Keep the source data in a table if useful, but enter the FILTER formula in ordinary worksheet cells outside the table.
Related
- Excel Dependent Drop-Down Lists in Microsoft 365
- Excel Dependent Drop-Down Lists With Spaces: Setup Guide
- Excel Dependent Drop-Down List: 3 Levels
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Sum Rows Based on Cell Value: Formulas That WorkTo total values in rows where a cell matches a condition, use Excel’s SUMIF function. With categories in…
- Excel Formula Returns FALSE Instead of a Value? Here’s WhyAn Excel formula often returns FALSE because an IF function has no value specified for the condition that…
- Can You Filter Out Duplicates in Excel? Methods That WorkYes. In Excel for Microsoft 365 on Windows, you can identify duplicates with conditional formatting, filter them with…
- Excel Formula for Every Friday Date in a Date Range ListWith a start date in A2 and an end date in B2, this Microsoft 365 formula returns every…
- Excel NPV of a Series of Cash Flows: Formula and ExamplesTo calculate the net present value of a cash-flow series in Excel, use =NPV(B1,B3:B5)+B2 when the discount rate…
- Excel COUNTIF and SUMIF With Dates: Practical ExamplesTo count or sum rows by date in Excel, use COUNTIF or SUMIF with a date criterion; for…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.