How to Use Excel FILTER Function with Two Criteria
Question details
The user needs an Excel formula to automatically return specific data based on two distinct conditions without manually filtering the source table.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Extracting a dynamic list of records matching multiple criteria from a large table to update automatically on another worksheet.
- Observed behavior
- The goal is to automatically extract matching data based on dual criteria (e.g., Severity equals C AND Type equals Dog) and have the results spill dynamically.
Ensure your data is formatted as an Excel Table (Ctrl+T) so formula references update dynamically, and verify that your spreadsheet software supports Dynamic Array functions.
Use the FILTER Function with Multiplication for AND Logic
Apply the FILTER function using the multiplication operator (*) to evaluate two criteria simultaneously, ensuring only rows meeting both conditions are returned.
The FILTER function dynamically extracts data based on conditions. When using multiple criteria requiring an 'AND' logic (where both conditions must be true), you enclose each condition in parentheses and multiply them together.
In array logic, TRUE evaluates to 1 and FALSE evaluates to 0. Multiplying them ensures that only rows where both conditions are TRUE (1 * 1 = 1) are successfully filtered and displayed.
Click on the cell in the new worksheet where you want the top-left corner of the filtered list to begin.
Type the formula =FILTER(myTable[Case No], (myTable[Severity]="C") * (myTable[Type]="Dog"), "No matches") into the formula bar.
Replace 'myTable', 'Case No', 'Severity', and 'Type' with the actual table name and column headers from your specific spreadsheet.
Press Enter to apply. The results will automatically spill down the column, generating a dynamic list of all items that match both conditions.
Filter and Analyze Data Dynamically with WPS Office
WPS Spreadsheet fully supports advanced dynamic array functions like FILTER, allowing you to seamlessly extract and manage data based on multiple criteria. It provides a powerful, fast, and highly compatible environment for all your data analysis needs.
- 1. Download and install WPS Office: Get the free WPS Office suite from the official website and complete the quick installation process.
- 2. Open your spreadsheet: Launch WPS Spreadsheets and open your existing workbook containing the source table.
- 3. Apply the FILTER formula: Select your target cell and enter the FILTER formula using the multiplication (*) operator to evaluate multiple conditions, exactly as you would natively.
Frequently Asked Questions
How do I use the Excel FILTER function for an 'OR' logic instead of 'AND'?
To return data if either one of the conditions is met (OR logic), use the addition operator (+) instead of multiplication (*). For example: =FILTER(myTable[Case No], (myTable[Severity]="C") + (myTable[Type]="Dog")).
Why am I getting a #CALC! error with my FILTER formula?
The #CALC! error usually appears if the FILTER function evaluates the conditions and finds no matching records, but you haven't provided a third argument. Add an 'if_empty' string at the end of your formula, such as "No matches".
Can I filter based on conditions from another worksheet?
Yes, the source table and the destination cell can be on entirely different worksheets. By formatting your source data as an Excel Table, you can simply use the table name and column headers in the formula without manually specifying sheet names.
