How to Use Excel FILTER Formula with Two Criteria
Question details
The user wants to extract specific records from a master data sheet into separate worksheets based on two conditions: job code and district.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Creating district-specific reports by filtering a master data table using two specific criteria.
- Observed behavior
- Needs a dynamic array formula that retrieves corresponding values from specific columns when both criteria are met.
Ensure you are using a spreadsheet version that supports dynamic array formulas, such as Microsoft Excel 365, Excel 2021, or the latest version of WPS Office.
Use the FILTER Function with Multiplication (*) for Multiple Criteria
The most efficient way to filter by two conditions is to multiply the criteria arrays within the FILTER function, which acts as an AND operator.
In dynamic array formulas, multiplying two conditions like `(Condition1) * (Condition2)` evaluates to TRUE only if both conditions are met. This allows the FILTER function to extract records that match both your specific district and job code.
Navigate to your district-specific worksheet and click on the top-left cell where you want the filtered data to appear.
Type the formula `=FILTER(DATA!I:I, (DATA!G:G="MRD") * (DATA!F:F="ABC123"), "No matching data")` into the formula bar.
Change `DATA!I:I` to the specific column you want to retrieve (e.g., `DATA!B:B`), and update the condition values ("MRD" and "ABC123") to match your actual criteria. You can also reference cells containing these values, such as `A1` and `C1`.
Press Enter. The formula will automatically spill the matching results down the column, retrieving only the rows where both conditions are met.
Filter Data with Multiple Criteria in WPS Office
WPS Spreadsheet provides powerful data analysis tools and supports advanced array formulas to help you manage complex datasets easily, all within a familiar and highly compatible interface.
- 1. Open your dataset: Launch WPS Spreadsheet and open your master data workbook containing the records.
- 2. Apply the FILTER formula: Use the identical `=FILTER(array, (include1) * (include2))` syntax to extract your data.
- 3. Organize your reports: Easily create district-specific sheets and reference your master data across tabs without lag.
Frequently Asked Questions
Can I use OR logic instead of AND logic in the FILTER function?
Yes. To use OR logic (where either one of the conditions is met), use a plus sign (+) instead of an asterisk (*). For example: `=FILTER(DATA!I:I, (DATA!G:G="MRD") + (DATA!F:F="ABC123"))`.
Why am I getting a #CALC! error with my FILTER formula?
A #CALC! error occurs when the FILTER function finds no records matching your criteria and you haven't provided a value for the [if_empty] argument. Adding a text string like "No matching data" at the end of your formula resolves this.
Why is my FILTER formula returning a #SPILL! error?
A #SPILL! error means there is existing data blocking the cells where the FILTER function needs to output its results. Clear the cells below the formula to allow the results to spill automatically.
Can I return multiple columns with the FILTER function?
Yes, you can return multiple columns by expanding the array reference in the first argument. For instance, using `DATA!A:I` instead of `DATA!I:I` will return all columns from A to I for the matching rows.
