Excel FILTER With Multiple Criteria: AND and OR

Formulas & functions · Updated November 19, 2026

To filter an Excel list by multiple conditions, use the FILTER function and combine its criteria: multiply conditions for AND, or add them and test for a positive result for OR. With columns A:E containing Name, Region, Product, Sales, and Status in rows 2:7, this returns rows where Region is North and Status is Open: =FILTER(A2:E7,(B2:B7="North")*(E2:E7="Open"),"No matches").

How FILTER handles multiple criteria

FILTER returns the rows or columns from an array that satisfy a corresponding include condition. In the example above, A2:E7 is the data to return, while each comparison produces a list of TRUE or FALSE values—one for each row. Multiplying the two lists makes a row eligible only when both comparisons are TRUE. Excel treats TRUE as 1 and FALSE as 0 in this arithmetic: 1 × 1 is 1, but any product involving 0 is 0.

The function’s general syntax is FILTER(array,include,[if_empty]). The optional third argument supplies a result when nothing matches; without it, an empty result can produce a #CALC! error. In the opening example, the message “No matches” appears instead. The result is a dynamic array: Excel places it in the formula cell and spills the matching rows into neighboring cells. It updates when the source data or criteria change.

FILTER is available in Microsoft 365 and Excel 2021 or later, including Excel 2024. Older perpetual versions such as Excel 2019 do not include the function. In Microsoft 365 for Windows, Mac, and the web, the formula works in the same general way; the exact interface for managing files and formulas can differ, but the criteria logic does not.

Use AND when every condition must be true

For AND logic, multiply the criteria arrays. Each additional requirement goes inside the include argument, joined with another multiplication sign. Suppose the same A:E data has sales in column D. To return rows where Region is North, Status is Open, and Sales are at least 700, use this formula in a clear area outside the source data:

With names and other fields in A2:E7, regions in B2:B7, sales in D2:D7, and statuses in E2:E7, use: =FILTER(A2:E7,(B2:B7="North")*(E2:E7="Open")*(D2:D7>=700),"No matches").

Using the example records in the opening section, the result contains Ana and Farah: both are in the North region, have Open status, and meet the sales threshold. Ben and Dev fail the region condition, Cara fails the status condition, and Eli fails the region condition. This row-by-row interpretation is a useful way to check a complicated formula: a row appears only if every multiplied condition evaluates to TRUE.

Comparison operators can test numbers, dates, and text. For example, use > for greater than, <= for less than or equal to, or <> for “not equal to.” For a date condition, compare the date column with a genuine Excel date or a cell containing one, rather than relying on text that merely looks like a date.

Use OR when any condition can match

For OR logic, add the condition arrays. Because a row matching both conditions produces 2, test whether the sum is greater than zero. With the same dataset, the following returns rows from either the North or South region:

With regions in B2:B7 and full rows in A2:E7, use: =FILTER(A2:E7,((B2:B7="North")+(B2:B7="South"))>0,"No matches").

The result includes Ana, Ben, Cara, Dev, and Farah, because each is in North or South; Eli is excluded because the region is West. The final >0 makes the intention clear and turns the summed values into a TRUE-or-FALSE include array. For two conditions that cannot both be true, such as Region equals North or Region equals South, the test is still helpful for readability.

Do not confuse OR with AND when combining comparisons. Multiplication means “all required”; addition followed by a positive test means “at least one.” If the requirement is “North and Open,” adding those conditions would include a row that meets only one of them, which is not the intended result.

Combine AND and OR with parentheses

Real filters often mix the two kinds of logic. For example, you might need rows from North where either the status is Open or sales are at least 1,000. The formula below groups the two alternatives first, then requires the North condition as well:

With Region in B2:B7, Sales in D2:D7, Status in E2:E7, and complete rows in A2:E7, use: =FILTER(A2:E7,(B2:B7="North")*(((E2:E7="Open")+(D2:D7>=1000))>0),"No matches").

In the sample records, this returns Ana, Dev, and Farah. Ana and Farah satisfy both the North and Open parts; Dev has sales of 1,500, so he satisfies the second alternative but not the North condition—therefore, he should not appear. Checking that outcome reveals why this example needs care: because Dev is not North, he is excluded. The actual matching rows are Ana and Farah. Cara is North but is Closed and has sales below 1,000, so she is excluded.

Parentheses are essential because they show which alternatives belong together. A reliable method is to translate the request into plain language first: “North AND (Open OR sales of at least 1,000).” Then put the OR comparisons inside parentheses and multiply that grouped result by the North comparison. In complex rules, add line breaks or build pieces in helper cells temporarily so each part can be checked.

Use cells for criteria that can change

Typing criteria such as “North” directly into a formula is convenient for a one-off result, but it is less flexible when users need to choose different values. Put a region choice in H2 and a status choice in H3, then refer to those cells in the formula. This lets someone change the selections without editing the formula itself.

With the source data in A2:E7, Region in B2:B7, Status in E2:E7, the chosen region in H2, and the chosen status in H3, use: =FILTER(A2:E7,(B2:B7=H2)*(E2:E7=H3),"No matches").

If either criteria cell may be left blank to mean “ignore this condition,” wrap each comparison in an IF test. A blank criterion returns 1 for every row, so it does not narrow the results; a populated criterion returns the corresponding comparison array:

With the same source and criteria-cell layout, use: =FILTER(A2:E7,IF(H2="",1,B2:B7=H2)*IF(H3="",1,E2:E7=H3),"No matches").

Here, the formula still returns only rows matching any criteria that have been supplied. If both H2 and H3 are blank, it returns every source row. If blank should instead mean “find blank cells in the source,” do not use this optional-criteria pattern: compare the relevant source column to an empty string explicitly. Decide which meaning blank has before sharing the workbook, because the two behaviors are different.

Text matching, dates, and case sensitivity

Direct equality checks such as B2:B7="North" are not case-sensitive in standard Excel comparisons, so “north” and “North” match. If capitalization must be distinguished, use EXACT, which performs a case-sensitive text comparison. For a contains search rather than an exact match, combine SEARCH with ISNUMBER; SEARCH finds text within a cell and is not case-sensitive.

With product descriptions in C2:C7 and full rows in A2:E7, this returns rows whose Product text contains “lap,” including a value such as “Laptop”:

With products in C2:C7 and the records in A2:E7, use: =FILTER(A2:E7,ISNUMBER(SEARCH("lap",C2:C7)),"No matches").

For a case-sensitive contains search, use FIND instead of SEARCH; wrap its result in ISNUMBER in the same way. These are substring searches, not wildcard criteria. If you need matching based on a more involved rule—such as a text pattern, a date window, or a condition derived from another calculation—build that rule as an array of TRUE/FALSE values and use it as FILTER’s include argument.

Prepare the data and handle common errors

FILTER works best when the data is a consistent rectangle with one header row and no merged cells within the records. Keep the header row out of the data range unless you deliberately want it returned as part of the result. Every range used to form an include array must have the same number of rows as the array being filtered. For example, filtering A2:E100 with a condition based on B2:B99 produces a size mismatch and an error.

For a growing dataset, converting the source range into an Excel Table can make the source easier to maintain: select a cell in the data and choose Insert > Table, then confirm whether the table has headers. Tables expand when rows are added. Structured references can make formulas easier to read, although the exact reference names depend on the table and its column headers. Put the FILTER formula outside the Table: spilled array formulas cannot spill inside a Table, and cells blocking the spill area can cause #SPILL!.

  • #CALC!: No rows matched and no if_empty result was supplied. Add a message such as “No matches” as the third argument.
  • #SPILL!: One or more cells in the intended output area are occupied, or the formula is inside an Excel Table. Clear the spill area or move the formula.
  • Wrong rows included: Check whether the request means AND or OR, and verify that mixed conditions are grouped with parentheses.
  • Unexpected results: Check for leading or trailing spaces, inconsistent data types, and ranges that do not start or end on the same rows. A number stored as text may not behave like a numeric value in comparisons.

FILTER is a formula, not the same thing as the ribbon’s regular column filter. The worksheet function returns a separate, live result range and leaves the source data in place. The Data tab’s filter controls hide nonmatching rows in the original list instead. Choose the formula when you need a reusable output range, and use the standard filter when you want to inspect or edit the original records interactively.

Frequently Asked Questions

Can Excel FILTER use more than two criteria?
Yes. Add another comparison to the include argument. Multiply it in when every condition must be true, or add it to an OR group and test whether the sum is greater than zero. Keep the ranges aligned and use parentheses to make mixed logic explicit.

Does FILTER support AND and OR functions directly?
You can build a FILTER condition with Boolean logic, but multiplying and adding criteria arrays is a common concise approach. The key is the meaning: multiplication represents AND, while addition followed by a greater-than-zero test represents OR. Parentheses help prevent mistakes in mixed conditions.

Why does FILTER show #SPILL!?
Excel cannot place the complete dynamic result in the cells around the formula. Clear any occupied cells in the intended output area, move the formula to an open area, and make sure it is not entered inside an Excel Table.

Can I filter for text that contains a word?
Yes. Use ISNUMBER(SEARCH("word",text_range)) as the include condition. SEARCH is case-insensitive; use FIND when capitalization should matter. Combine that condition with other criteria using multiplication or addition as appropriate.

Can I use FILTER in Excel 2019?
No. FILTER is available in Microsoft 365 and Excel 2021 or later, including Excel 2024, but not Excel 2019. In older versions, use the Data tab’s filter tools or another compatible method, such as a helper column with AutoFilter.

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.