How to Use the FILTER Function in Excel with Multiple AND and OR Criteria

The FILTER function in Microsoft Excel represents one of the most transformative dynamic array formulas available in modern spreadsheet workflows. Unlike traditional filtering mechanisms that require manual clicking or complex array combinations of INDEX and MATCH, FILTER dynamically extracts matching records from an source dataset and outputs them into a designated spill range. As underlying data changes, the filtered output updates automatically without recalculation delays.

While extracting records based on a single condition is straightforward, real-world data analysis regularly requires evaluating multiple intersecting conditions. In Excel, combining criteria within the FILTER function relies on Boolean logic principles rather than standard logical functions like AND() or OR(). This guide explains how Boolean arithmetic governs multi-criteria filtering, details how to construct intersecting (AND) and alternative (OR) logic, and demonstrates practical formula structures to solve common business reporting problems.

Understanding Boolean Logic in Dynamic Array Formulas

A frequent mistake when working with the FILTER function is attempting to insert standard Excel logical functions, such as AND(condition1, condition2) or OR(condition1, condition2), directly into the include argument. Standard logical functions aggregate an entire array of values into a single collective TRUE or FALSE outcome. Because the include parameter requires an array of individual Boolean evaluations corresponding to each row of the dataset, using AND() or OR() causes the formula to fail or return an incorrect single-cell calculation.

To evaluate criteria row by row across arrays, Excel uses Boolean mathematical operators:

• Multiplication (*) represents AND logic: When two conditions are multiplied, Excel treats TRUE as 1 and FALSE as 0. The product is 1 (TRUE) only when all criteria evaluate to 1 (1 * 1 = 1). If any condition evaluates to FALSE (0), the resulting product is 0.
• Addition (+) represents OR logic: When two conditions are added together, any row where at least one condition evaluates to TRUE results in a sum greater than zero (1 + 0 = 1 or 1 + 1 = 2). In Excel formula evaluation, any non-zero numeric value is interpreted as TRUE.

Constructing the Basic Syntax for the FILTER Function

The fundamental structure of the FILTER function consists of three parameters:

=FILTER(array, include, [if_empty])

• array: The contiguous range, table, or selected columns containing the dataset to be filtered (for example, A2:E100).
• include: A Boolean array containing the conditional logic that determines which rows appear in the spill range.
• if_empty: An optional argument specifying what to display if no rows meet the specified conditions (for example, "No matching records found"). Always configure this parameter to avoid unsightly #CALC! errors.

Filtering Data with Multiple AND Criteria

When every specified condition must be satisfied simultaneously for a row to be returned, enclose each individual logical test in parentheses and multiply them together using the asterisk (*) operator.

Formula Structure for AND Conditions

=FILTER(A2:D100, (B2:B100 = "Electronics") * (D2:D100 >= 500), "No matching records")

Step-by-Step Implementation

1. Click on the top-left destination cell where you want the filtered spill range to begin. Ensure there are adequate empty columns and rows below and to the right of this cell to prevent #SPILL! errors.
2. Enter =FILTER( and select the data range, such as A2:D100.
3. Type a comma, open a parenthesis, and enter the first logical condition: (B2:B100 = "Electronics").
4. Enter the multiplication operator (*), open a second parenthesis, and enter the second logical condition: (D2:D100 >= 500).
5. To add a third condition (such as verifying a region in column C), append another multiplication operator and bracketed test: * (C2:C100 = "North").
6. Type a comma, specify the fallback text "No records found", close the final parenthesis, and press Enter.

Excel evaluates every row independently. If Row 5 contains “Electronics” in Column B and a sales figure of 750 in Column D, the condition evaluates to (TRUE) * (TRUE), which equates to 1 * 1 = 1. Consequently, Row 5 is included in the output.

Filtering Data with Multiple OR Criteria

When a record should be displayed if it meets any one of several conditions, combine the bracketed logical tests using the addition (+) operator.

Formula Structure for OR Conditions Across Different Columns

If you want to extract transactions where either the Department is “Hardware” or the Total Sales exceeds 1,000, structure the formula as follows:

=FILTER(A2:D100, (B2:B100 = "Hardware") + (D2:D100 > 1000), "No records found")

Formula Structure for Multiple Values in the Same Column

When testing whether a single column matches one of multiple specific text values (for example, finding orders from “London”, “Manchester”, or “Edinburgh”), add the individual equality checks together:

=FILTER(A2:D100, (C2:C100 = "London") + (C2:C100 = "Manchester") + (C2:C100 = "Edinburgh"), "No matching cities")

Alternatively, if you are checking against an extensive list of allowed values, you can combine FILTER with the ISNUMBER(MATCH()) idiom, which provides cleaner syntax:

=FILTER(A2:D100, ISNUMBER(MATCH(C2:C100, {"London", "Manchester", "Edinburgh"}, 0)), "No matching cities")

Combining AND and OR Logic in a Single Formula

Sophisticated data models often demand a combination of both intersecting and alternative criteria. For example, you may need to extract all sales transactions from the “North” region that represent either the “Corporate” or “Enterprise” customer tier.

Because mathematical operations adhere to standard operator precedence (multiplication occurs before addition), you must group alternative conditions inside additional nested parentheses to ensure the OR operation is evaluated before the AND multiplication.

Syntax for Grouped AND and OR Conditions

=FILTER(A2:E100, (B2:B100 = "North") * ((C2:C100 = "Corporate") + (C2:C100 = "Enterprise")), "No records found")

In this construction:

1. Excel first evaluates ((C2:C100 = "Corporate") + (C2:C100 = "Enterprise")) for each row, returning 1 if either condition is met and 0 if neither is met.
2. Excel then multiplies that intermediate result by the first test (B2:B100 = "North").
3. Only rows that satisfy the region requirement AND at least one of the tier requirements produce a final result of 1.

Practical Troubleshooting and Edge Cases

When working with dynamic multi-criteria filters, several standard errors can interrupt formula execution:

• #SPILL! Error: Occurs when existing text, numbers, or merged cells obstruct the cells that the filtered output needs to populate. Clear all data from the destination cells to allow the array to expand.
• #VALUE! Error: Typically caused by mismatched array dimensions. Ensure that every condition array (e.g., B2:B100) matches the exact start and end row boundaries of the source array (e.g., A2:E100).
• Parentheses Mismatches: Every individual condition must have its own opening and closing parenthesis before applying the * or + operators. Omitting an internal parenthesis alters the mathematical precedence and produces invalid Boolean logic.
• Case Sensitivity: Standard comparison operators in Excel (=) are not case-sensitive. If you require case-sensitive filtering, incorporate the EXACT function into the logical expression: =FILTER(A2:D100, EXACT(B2:B100, "UK-North"), "None").

Mastering Boolean arithmetic inside the FILTER function eliminates the need for auxiliary helper columns, streamlining spreadsheet architecture and ensuring analytical dashboards respond instantly to fluctuating operational data.