Excel FILTER Function: Multiple Criteria and Examples

Formulas & functions · Updated November 20, 2026

Yes. In Microsoft Excel for Microsoft 365, use the FILTER function with multiplication (*) to require multiple conditions at once (AND), or addition (+) to accept a row that meets any condition (OR). For example, with records in A2:D100, region in column B, and status in column C, use =FILTER(A2:D100,(B2:B100="West")*(C2:C100="Open"),"No matches") to return rows where the region is West and the status is Open.

How FILTER handles multiple criteria

FILTER returns the rows or columns from an array for which a corresponding include test is TRUE. 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 if_empty is an optional value to show when nothing qualifies. In the opening example, each comparison creates a series of TRUE and FALSE results, one for each record.

Excel treats TRUE as 1 and FALSE as 0 in arithmetic. Multiplying the two tests keeps a row only when both are TRUE: 1 multiplied by 1 is 1, while any combination containing 0 gives 0. The include array therefore has to line up with the rows in the source array. If the source has 99 records, each criteria range should cover those same 99 rows.

The function returns a dynamic array: matching records appear in the formula cell and spill into adjacent cells. The source data is not rearranged or hidden, as it is when you apply the regular worksheet filter. FILTER creates a separate result that updates as the underlying values change.

Require every condition with AND

Use * between comparisons when a row must pass all conditions. With data in A2:D100, the region in B, status in C, and sales amount in D, this example returns West-region records that are Open and have sales of at least 500:

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

All three tests must be true for the product to be 1. A record with West and Open but sales of 420 will not appear; neither will a record with sales of 700 and a Closed status. The number 500 is a threshold, not text, so it is written without quotation marks.

You can add further tests by multiplying in another same-sized condition. Common examples include a department, a minimum quantity, a category, or a date range. Keep each comparison inside parentheses: this makes the intended logic clear and avoids confusion about how Excel evaluates operators.

Match any condition with OR

Use + when a row can meet one condition or another. With records in A2:D100 and region names in B2:B100, this formula returns rows for either the West or East region:

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

Addition produces a positive result if at least one comparison is TRUE, so either region qualifies. A row does not appear twice if it meets two OR tests: FILTER evaluates each source row once and returns that row once. This is different from stacking two separate FILTER results, which could duplicate a record if the result sets overlap.

For more alternatives in the same field, add another comparison in parentheses. OR logic is useful for requests such as West or East, Pending or In Progress, or any of several product codes. If the user should be able to choose one of many options from a list, a longer set of comparisons can become hard to maintain; the alternatives section below explains another approach.

Combine AND and OR conditions

Parentheses let you group alternatives before requiring another test. With records in A2:D100, region in B, and status in C, this returns Open records from either the West or East region:

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

The inner addition means West or East; multiplying by the status test means the record must also be Open. Think of the logic as (West OR East) AND Open. Without the parentheses around the regional alternatives, the expression may not reflect the intended grouping, so make the logic explicit even when a shorter expression appears to work.

It can help to write the request in plain language first. Mark each group of alternatives with OR, then identify which groups all have to be satisfied with AND. Translate OR groups into additions and AND joins into multiplication. Test the formula on a few records where you already know whether each one should qualify.

Use cells to make criteria changeable

Instead of hard-coding West and Open in a formula, place the selected region in H1 and selected status in H2. With the same records in A2:D100, region in B, and status in C, use:

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

Changing either selector cell updates the returned records without editing the formula. This is useful for a small report or dashboard. Keep the criteria cells outside the spill area, and make sure their contents match the source values: an extra space or a different spelling can prevent a match.

When users need to pick criteria from a controlled set, Excel’s Data Validation feature can provide a drop-down list. On Windows, the command is Data > Data Validation. A drop-down reduces accidental spelling differences, but it does not change how FILTER evaluates the criteria. On Mac, the Data Validation command is also on the Data tab; the exact ribbon layout can vary with the Excel window and version.

Filter dates, numbers, and partial text

For a date interval, use a lower boundary inclusive and an upper boundary exclusive. Suppose dates are in D2:D100, the complete records are in A2:D100, the start date is in H1, and the day after the desired final date is in H2. Use:

=FILTER(A2:D100,(D2:D100>=H1)*(D2:D100<H2),"No matches")

If H1 is January 1 and H2 is February 1, the result includes all dates in January. Using a less-than next-month boundary also includes times on the final day if the source contains date-and-time values. Excel stores dates as numbers, so confirm that the source cells contain real Excel dates rather than text that merely looks like a date.

To combine a date interval with a status, multiply the date tests by the status comparison. For partial text matches, SEARCH can find a word within a cell. With names or descriptions in A2:A100 and full records in A2:D100, this returns rows whose first column contains bolt, without distinguishing uppercase from lowercase:

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

SEARCH returns a number when it finds the text, and ISNUMBER converts that result into a TRUE test. It is not the same as an exact match: a description such as stainless bolt kit qualifies. For case-sensitive searching, FIND is the case-sensitive counterpart to SEARCH; check the result carefully if the search text may be missing from some rows.

Spill behavior and common problems

Enter the formula in an empty area with enough room for all returned columns and rows. If another value, merged cell, or obstruction occupies part of the required output range, Excel displays #SPILL!. Clear the obstructing cells or move the formula. A result can grow when the source changes, so leave a reasonable open area around the formula rather than placing totals directly below it.

  • No matches: Supply the optional if_empty argument, such as the text No matches, to avoid the #CALC! error Excel can return when the result is empty.
  • Different range sizes: Make each criteria range the same height as the source records. For example, pairing A2:D100 with B2:B99 misaligns the include array.
  • Unexpected omissions: Check for spaces, inconsistent labels, text-formatted numbers, and date values stored as text. Exact equality tests do not ignore these differences.
  • Formula shows as text: Check that the cell is not formatted as Text and that the formula begins with an equals sign.

FILTER’s returned array cannot spill inside an Excel Table, even though a table can be used as the source. Put the formula in ordinary worksheet cells outside the table. If a workbook is shared with people using older Excel editions that do not support dynamic arrays, they may not be able to calculate the function normally.

Tables, versions, and alternatives

Excel Tables can make source ranges easier to maintain because their references expand when records are added. Suppose a table named SalesTable has columns Region and Status, and the selection cells are H1 and H2. Enter this formula in a regular cell outside the table:

=FILTER(SalesTable,(SalesTable[Region]=H1)*(SalesTable[Status]=H2),"No matches")

FILTER is available in Microsoft 365 and Excel 2021 or later, including current Excel for Mac and Excel for the web. The function is not available in older perpetual editions such as Excel 2019. Menu placement differs somewhat across platforms, but the formula logic is the same in supported versions. If a workbook must work in older Excel, use the built-in Data > Filter command to filter the source, or consider a PivotTable for a summary-oriented report. Those options do not create the same automatically spilled formula result.

For many alternatives, a helper column or a lookup list can be easier to read than a very long chain of additions. Another option is COUNTIF against a list of permitted values, but array behavior and exact setup depend on the shape of that list. Start with the simplest formula that clearly expresses the intended rule; a complex formula is not automatically more reliable. The worksheet’s Data > Filter feature is also a practical choice when users need to inspect and change filters interactively rather than build a separate output range.

Frequently Asked Questions

Can FILTER use multiple criteria from different columns?
Yes. Multiply the comparisons to require all of them, or add comparisons for alternatives. Make sure every compared range aligns with the rows in the array being returned.

Does * mean AND and + mean OR in FILTER?
They are a common way to combine TRUE/FALSE arrays in Excel formulas: multiplication acts like AND, and addition acts like OR. Use parentheses to show the intended grouping, especially when mixing both operators.

Why does FILTER return #CALC!?
Most often, no rows met the include condition and the formula did not specify an if_empty result. Add a third argument such as No matches, or check that the criteria genuinely match the source values.

Can I use multiple criteria on the same column?
Yes. Use addition for alternatives such as West or East. Use multiplication for a range of numeric or date values, such as a value at least 10 and less than 20.

Does FILTER change or hide the original data?
No. It returns a separate dynamic result and leaves the source rows in place. Use the regular Data > Filter command if you want to hide nonmatching rows in the original list instead.

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.