Excel FILTER Formula with Multiple Criteria Explained

Formulas & functions · Updated November 19, 2026

In Excel for Microsoft 365, use FILTER with multiplication between conditions when every criterion must be true, and addition when any one criterion may be true. For example, with records in A2:D8, regions in B2:B8 and statuses in C2:C8, =FILTER(A2:D8,(B2:B8="East")*(C2:C8="Open"),"No matches") returns rows that are both in the East region and Open.

What the FILTER formula does

FILTER returns matching rows or columns from a range, leaving the source data unchanged. Its syntax is =FILTER(array,include,[if_empty]): array is the data to return, include is a TRUE-or-FALSE test for each row or column, and the optional third argument supplies a result when nothing matches. The function is available in Microsoft 365 and Excel 2021 or later, including Excel for the web. It is not available in Excel 2019 or earlier.

For the examples below, imagine a worksheet with these columns and sample records. The headers are in row 1; data occupies rows 2 through 8.

Row A: Order B: Region C: Status D: Amount
2 101 East Open 120
3 102 West Open 90
4 103 East Closed 75
5 104 East Open 200
6 105 North Open 110
7 106 West Closed 150
8 107 East Open 95

Enter a formula in an empty cell outside the source data, such as F2. Excel spills the matching records into adjacent cells automatically. Leave enough blank space for the results; a value or merged cell in the spill area can cause a #SPILL! error.

Use AND logic when every condition must match

In a FILTER include test, multiplying condition arrays combines them as AND: TRUE is treated as 1 and FALSE as 0, so a row is included only when every multiplied test is true. This is the usual choice for requests such as “East region and Open status.”

With the sample data in A2:D8, regions in B2:B8 and statuses in C2:C8, the formula below returns orders 101, 104 and 107, because those are the records meeting both tests.

=FILTER(A2:D8,(B2:B8="East")*(C2:C8="Open"),"No matches")

You can add more AND conditions by multiplying another test. For instance, a numeric condition can restrict the results to orders with an amount over 100. The comparisons are made row by row, so each condition range must line up with the corresponding records in the array.

With orders in A2:D8, amounts in D2:D8, regions in B2:B8 and statuses in C2:C8, this returns orders 101 and 104: both are East and Open, and both exceed 100.

=FILTER(A2:D8,(B2:B8="East")*(C2:C8="Open")*(D2:D8>100),"No matches")

Use OR logic when any condition may match

Addition combines tests as OR: a row is retained if at least one condition is true. A sum greater than zero expresses that rule clearly. This is useful for matching one of several categories in the same column, such as East or West.

With the sample records in A2:D8 and regions in B2:B8, the formula returns orders 101, 102, 103, 104, 106 and 107. It excludes order 105 because its region is North.

=FILTER(A2:D8,((B2:B8="East")+(B2:B8="West"))>0,"No matches")

For only two OR tests, the comparison to zero is not strictly necessary because FILTER can use nonzero values as true, but including it makes the intended test explicit and is helpful when combining more logic. Parentheses matter: group the additions so Excel evaluates the OR group before combining it with any other condition.

Combine AND and OR criteria

A common request is “East or West, and Open.” The OR group should be calculated first; then multiply that result by the Open-status condition. Without the grouping parentheses, Excel may apply the operators in an order that does not represent the requested logic.

With orders in A2:D8, regions in B2:B8 and statuses in C2:C8, this returns orders 101, 102, 104 and 107. Order 103 is East but Closed, and order 106 is West but Closed, so neither meets the AND portion.

=FILTER(A2:D8,(((B2:B8="East")+(B2:B8="West"))>0)*(C2:C8="Open"),"No matches")

It often helps to translate a request into plain-language logic before writing the expression. Mark each part as “all of these” (AND) or “at least one of these” (OR), then build and parenthesize each group. For example, “Open and (East or West)” is different from “(Open and East) or West”; the latter would also include closed West records.

Make criteria editable with worksheet cells

Hard-coded text is convenient for a one-off result, but referencing cells makes the filter reusable. Put a desired region in G2 and a desired status in H2, then change those cells whenever you want a different result. Text must match the values in the data; extra spaces can make a seemingly correct criterion fail.

With records in A2:D8, region values in B2:B8, status values in C2:C8, the selected region in G2 and selected status in H2, use this formula:

=FILTER(A2:D8,(B2:B8=G2)*(C2:C8=H2),"No matches")

To let users choose between two regions, put the choices in G2 and G3. This OR test returns records for either selected region. If the same choice is entered twice, a matching row still appears only once because FILTER selects source rows rather than appending one result for each condition.

With regions in B2:B8 and the two requested region names in G2:G3, use:

=FILTER(A2:D8,((B2:B8=G2)+(B2:B8=G3))>0,"No matches")

Filter by number, date, or partial text

Criteria do not have to be exact text matches. Numeric comparisons such as greater than, less than, or equal to can be combined with text tests. For example, a sales report might require East records with amounts of at least 100. The operator belongs inside the comparison; for a threshold stored in a cell, compare the amount range directly with that cell.

With amounts in D2:D8, regions in B2:B8, a minimum amount in G2 and the full data in A2:D8, this returns East orders with amounts greater than or equal to the threshold:

=FILTER(A2:D8,(B2:B8="East")*(D2:D8>=G2),"No matches")

For dates, compare a date column with a real Excel date value or a cell containing one, rather than relying on text that only looks like a date. A date range is usually expressed as two AND tests: on or after the start date and before or on the end date. Ensure the source cells contain numeric Excel dates; text dates can behave unexpectedly.

For partial text matching, the SEARCH function can find a word within a cell. It is not case-sensitive. If you use this approach, handle rows without the word carefully because SEARCH returns an error when it finds no match; an error in the include array can make FILTER return an error too. For a simple exact category filter, direct equality comparisons are easier to read and less prone to surprises.

Return selected columns and handle empty results

The array argument determines which fields appear in the output. If you want only the order number and amount, you can build an array from those two columns with CHOOSE, while keeping the include tests aligned to the source rows. This is useful when the source table has many fields but the report needs only a few.

With order numbers in A2:A8, amounts in D2:D8, regions in B2:B8 and statuses in C2:C8, the formula below returns two columns for East Open records:

=FILTER(CHOOSE({1,2},A2:A8,D2:D8),(B2:B8="East")*(C2:C8="Open"),"No matches")

The optional third argument is a single value or message for the no-match case. Supplying it avoids the #CALC! result that FILTER normally produces when no rows meet the conditions. The message does not mean the source data is wrong; it simply tells the reader there are currently no matching rows.

FILTER returns an array, not a permanently copied report. If source values change, the spilled result updates. You can format the output cells, but do not type into the middle of the spill range. If a fixed snapshot is needed, copy the results and use Paste Special > Values to place static values elsewhere.

Common errors and practical checks

When a formula does not return the expected records, check the include ranges and the source data before rewriting the logic. The most frequent problems are mechanical rather than mathematical:

  • Unequal range sizes: the array might contain rows 2 through 100 while a condition tests rows 2 through 99. Make the ranges the same height and ensure their first rows refer to the same record.
  • Unexpected spaces: East and a cell containing East followed by a space are different text values. TRIM can remove ordinary leading and trailing spaces when cleaning source data.
  • Wrong logic grouping: use multiplication for AND and grouped addition for OR. Add parentheses around OR groups in mixed expressions.
  • Errors in source cells: an error in a tested cell may propagate through the include array. Find and correct the underlying error instead of assuming it is a no-match result.
  • Blocked spill range: clear the cells where the output needs to appear, and check for merged cells that obstruct the result.
  • Using a table reference incorrectly: Excel tables can expand as records are added, but FILTER’s dynamic result should be placed outside the table because spilled array formulas are not supported inside table cells.

Excel’s standard formula argument separator in the examples is a comma. Some regional settings use semicolons instead, so Excel may require that separator when entering a formula; this is a regional setting difference, not a different filtering method. On Mac and Excel for the web, the FILTER logic is the same, although interface details and keyboard shortcuts can differ.

FILTER formula versus the Data tab filter

The worksheet FILTER function and Excel’s built-in filter controls solve related but different problems. FILTER creates a formula-driven result in another location and updates as its source changes. It is suitable for a report, dashboard, or a second view that needs several criteria and can be referenced by other formulas.

The built-in AutoFilter hides nonmatching rows in the original list. Use it when you want to inspect or edit the source records directly, or when you do not need a separate spilled result. Select a cell in the dataset and choose Data > Filter to add drop-down controls to the header row; you can then select values or apply text, number, or date filters. This does not create a separate formula result.

For a reusable output, FILTER is usually more transparent because the criteria are visible in the formula or in dedicated cells. For a quick manual inspection, AutoFilter may be simpler. Both can coexist, but filtering the source does not change the rows returned by a FILTER formula: that formula evaluates its referenced ranges, including rows hidden by AutoFilter.

Frequently Asked Questions

How do I use multiple criteria in Excel FILTER?
Multiply condition tests for AND, and add tests for OR. For mixed logic, group the OR tests in parentheses before multiplying by the AND tests. Make each test range the same size as the data array.

Does FILTER use AND or OR by default?
Neither; you create the logic in the include argument. Multiplication combines conditions as AND, while addition combines them as OR. Parentheses make mixed expressions easier to understand and prevent logic mistakes.

Can FILTER match several values in one column?
Yes. Add comparisons for the allowed values and test whether their sum is greater than zero. For a long or changing list of choices, keep the choices in cells rather than hard-coding each value in the formula.

Why does my FILTER formula return #CALC!?
A common reason is that no rows match and the optional if_empty argument was omitted. Add a message such as No matches as the third argument, then confirm that the criteria text, dates, and numbers really match the source values.

Why do I get #SPILL! from FILTER?
The output array may be blocked by existing content, merged cells, or another obstruction in the cells where results would spill. Clear that area or move the formula to a location with enough empty space.

Can older versions of Excel use FILTER?
FILTER is available in Microsoft 365 and Excel 2021 or later, but not Excel 2019 or earlier. In versions without the function, use Data > Filter for interactive filtering, or build a different formula-based solution suited to that version.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.