Excel FILTER Formula for Multiple Criteria

To filter rows in Excel by multiple criteria, use FILTER with a condition for each test: multiply the conditions for AND logic, or add them for OR logic. With records in A2:D100, regions in B2:B100 and statuses in C2:C100, this returns rows that are both East and Open: =FILTER(A2:D100,(B2:B100="East")*(C2:C100="Open"),"No matches").

What FILTER Does With Multiple Criteria

FILTER returns the rows or columns from an array that meet a condition. Its basic syntax has an array to return, an include test, and an optional value to show when nothing matches. In Microsoft 365 for Windows, the result spills into neighboring cells automatically; it is not necessary to copy the formula down. FILTER is also available in Excel 2021 and Microsoft 365 on Mac and the web, though buttons and interface details can differ. Regional settings may require semicolons instead of commas between arguments.

The include test can be one condition or a calculation combining several conditions. Each test usually produces a series of TRUE or FALSE values, one for every row in the source data. Excel treats TRUE as 1 and FALSE as 0 in arithmetic: multiplying tests keeps a row only when every test is TRUE, while adding tests keeps it when at least one test is TRUE.

For a concrete example, imagine A1:D5 contains Order, Region, Status, and Amount as its headers, with these four records: 104, East, Open, 240; 105, West, Open, 180; 109, East, Open, 125; and 112, East, Closed, 310. With the data in A2:D5, the East-and-Open example above returns orders 104 and 109, including all four columns. This shows why the criteria ranges and the returned range need to cover the same rows.

Use Multiplication for AND Criteria

Multiplication is the standard way to require multiple conditions at once. In the first example, a row must have East in the Region column and Open in the Status column. A row that meets just one test receives a zero after the two tests are multiplied, so FILTER excludes it. This pattern works with two tests or several.

For example, to add a minimum amount, with order data in A2:D100, region in B2:B100, status in C2:C100, amount in D2:D100, and the threshold in F1, use:

=FILTER(A2:D100,(B2:B100="East")*(C2:C100="Open")*(D2:D100>=F1),"No matches")

Each comparison is evaluated row by row. The amount test uses greater than or equal to, so a record exactly at the threshold qualifies. You can add more parenthesized comparisons with multiplication when a record must satisfy every additional requirement. Keep the logic readable: a long expression can be difficult to audit, so consider using named ranges or a helper column if the criteria become complicated.

Use Addition for OR Criteria

Addition means “this condition or that condition.” Suppose a report should include Open orders from either East or West. The region tests are added inside parentheses, and that combined test is multiplied by the status test. The result therefore requires Open status, but accepts either of the two regions.

With the order records in A2:D100, region in B2:B100, and status in C2:C100, use:

=FILTER(A2:D100,((B2:B100="East")+(B2:B100="West"))*(C2:C100="Open"),"No matches")

Parentheses matter because they make the intended grouping clear. Without grouping, a formula may evaluate conditions in an unintended order. For three acceptable regions, add a third comparison within the region group. For a rule that accepts either a certain region or a certain status, combine the relevant tests with addition instead; read the logic carefully, since that would include rows meeting either test, not necessarily both.

Mix AND and OR Conditions

Real requests often combine the two kinds of logic: “East or West, and Open, and at least the target amount.” Build each OR group with addition, then multiply that group by the conditions that must also be true. Add parentheses around each group so the formula reflects the rule in plain language.

With data in A2:D100, region in B2:B100, status in C2:C100, amount in D2:D100, and the minimum amount in F1, use:

=FILTER(A2:D100,((B2:B100="East")+(B2:B100="West"))*(C2:C100="Open")*(D2:D100>=F1),"No matches")

Before writing a mixed-condition formula, translate the request into a short rule such as “(East or West) and Open and amount at least 200.” Then make the parentheses match that rule. This is a simple way to catch a common mistake: adding all comparisons when some of them are meant to be mandatory, or multiplying alternatives that should be interchangeable.

Use Cells for Changeable Criteria and Dates

Hard-coded values are convenient for a one-off filter, but criteria in worksheet cells make a report easier to reuse. For example, put the chosen region in G1 and the chosen status in G2. With records in A2:D100, regions in B2:B100 and statuses in C2:C100, use:

=FILTER(A2:D100,(B2:B100=G1)*(C2:C100=G2),"No matches")

Changing either input cell updates the returned rows. This can be useful for a small report interface: label the input cells clearly, use data validation lists if you want controlled choices, and place the spilled results somewhere with enough empty space. Blank criteria cells are not automatically ignored; an empty cell compared with a data range tests for blank values. If users may leave a criterion empty, make that behavior explicit in the formula or provide a separate option for “all.”

Dates work similarly, provided the cells contain real Excel dates rather than text that only looks like a date. If the date range is in D2:D100, regions are in B2:B100, the selected region is in G1, and the start and end dates are in G2 and G3, use:

=FILTER(A2:D100,(B2:B100=G1)*(D2:D100>=G2)*(D2:D100<=G3),"No matches")

This includes both endpoints when the source values are date-only values. If the source includes times as well as dates, an end-date comparison to midnight may exclude later times on that final day. In that case, compare against the day after the end date using a less-than test, or otherwise define the desired time boundary carefully.

Find Text Matches and Exclude Blanks

An equality test such as Region equals East looks for an exact cell value. To find text that contains a word or phrase, use SEARCH to locate it and ISNUMBER to turn a successful search into TRUE. SEARCH is not case-sensitive; use FIND instead if case-sensitive matching is required.

With descriptions in A2:A100, complete rows to return in A2:D100, and the search phrase in F1, use:

=FILTER(A2:D100,ISNUMBER(SEARCH(F1,A2:A100)),"No matches")

SEARCH returns a position when it finds the phrase and an error when it does not; ISNUMBER converts those outcomes to TRUE or FALSE for FILTER. If F1 is blank, the search can match every text cell, so set expectations for blank input rather than assuming it will return no results. To exclude records with a blank value in column C while also requiring East in column B, use:

=FILTER(A2:D100,(B2:B100="East")*(C2:C100<>""),"No matches")

That blank test checks for an empty cell or a formula result that is an empty string. It does not treat a cell containing spaces as blank; those values may need cleaning first.

Choose an Empty-Result Message and Troubleshoot

The third FILTER argument controls what appears if no row meets the criteria. A short message such as “No matches” is clearer in a report than the default #CALC! error. The message is not a condition and does not change which rows qualify. If you prefer a blank result, use an empty text value as the third argument.

When a result looks wrong or an error appears, check these points:

Make sure the returned range and every criteria range have the same number of rows. A2:D100 cannot be filtered by a condition covering B2:B50.
Check for extra spaces, inconsistent spelling, or numbers stored as text. “East” and “East ” are not identical values.
Confirm that the comparison uses the intended operator: greater than, greater than or equal to, and not equal to have different meanings.
Look for occupied cells in the spill area. A blocked destination can produce a #SPILL! error; move or clear the obstructing content.
Check the source for errors. Errors in the array or include calculation can flow into the result.
Verify that the range references move or stay fixed as intended when copying a formula. Use absolute references where needed.

FILTER returns a dynamic array, so do not type inside the area where its results need to spill. If the source data grows regularly, an Excel Table can make references expand as records are added, although structured-reference syntax may look different from the range examples here.

When to Use FILTER Instead of Other Methods

FILTER is a good choice when the goal is to display a live, formula-driven subset of data elsewhere on a worksheet. It updates when source data or criteria change and keeps the source rows intact. It requires a version that supports dynamic arrays, including Excel 2021 and Microsoft 365; in older Excel versions, the function may not be available.

For a quick one-time inspection, the built-in column filters may be simpler. Select the data and choose Data > Filter, then choose values or conditions from the column dropdowns. That hides rows in place rather than generating a separate dynamic result. A PivotTable is better suited to summarizing or grouping large data sets, while Power Query is useful for repeatable import and transformation steps. These tools solve related but different problems; choose based on whether you need a live extracted list, an interactive view, a summary, or a repeatable data-cleaning process.

Frequently Asked Questions

Can FILTER use multiple criteria in Excel?
Yes. Combine row-by-row conditions in FILTER’s include argument. Multiply conditions when all must be true, and add them when any one of them can be true.

Does multiplication mean AND in an Excel FILTER formula?
Yes. When each comparison produces TRUE or FALSE, multiplying them keeps rows where every comparison is TRUE. Put each comparison in parentheses to make the logic clear.

How do I use OR criteria with FILTER?
Add the comparisons that represent alternatives, then use parentheses around that group. Multiply the group by any other tests that must also be satisfied.

Why does FILTER return #CALC!?
Usually, no rows met the condition and the optional if-empty argument was omitted. Add a message as FILTER’s third argument, or review the criteria for spelling, data type, and range mismatches.

Why am I getting #SPILL! from FILTER?
Excel cannot place the full result because one or more cells in the spill area are occupied, merged, or otherwise unavailable. Clear or move the obstruction, then the result can expand.

Can I filter by multiple criteria without FILTER?
Yes. Use Data > Filter for a manual view, or consider a PivotTable or Power Query for summaries and repeatable transformations. Those alternatives do not behave exactly like a formula that spills matching records into a separate range.

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.