How to Use Excel FILTER Formula with Greater Than and Less Than Criteria
Question details
The user wants to filter a dataset based on two numeric conditions: one column being greater than a specific value and another column being less than a specific value.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Filtering records that meet multiple numeric criteria simultaneously using the FILTER function.
- Observed behavior
- The user needs the correct formula syntax to apply multiple conditions (AND logic) without treating the numeric operators as text.
Ensure that your dataset is organized in contiguous columns and rows, and verify that the columns you are evaluating contain numeric values rather than text.
Using the FILTER Function with Multiplication for AND Logic
Combine multiple criteria using the multiplication operator (*) to apply AND logic within the FILTER function.
When using the FILTER function with multiple conditions, you must enclose each logical condition in parentheses and multiply them together. The multiplication symbol (*) acts as an AND operator in array formulas. Additionally, numeric operators like > and < must be written directly into the formula and not wrapped in quotation marks.
Click on the empty cell where you want the filtered data to begin spilling (for example, cell M5).
Type the formula =FILTER(B2:C4369,(B2:B4369>100)*(C2:C4369<10),"") into the formula bar.
The first argument B2:C4369 is the array to filter. The second argument (B2:B4369>100)*(C2:C4369<10) checks if column B is greater than 100 AND column C is less than 10. The third argument "" returns an empty string if no records match.
Press Enter to execute the formula. The filtered results will automatically spill into the adjacent cells.
Filter Data Effortlessly with WPS Office
WPS Spreadsheet fully supports dynamic array functions like FILTER, allowing you to extract data based on complex criteria easily. It offers a highly compatible and user-friendly environment for all your data analysis needs.
- 1. Open your dataset in WPS Spreadsheet: Launch WPS Office and open your workbook containing the data you want to filter.
- 2. Apply the FILTER formula: Select an empty cell and input your FILTER formula with multiple criteria using the * operator for AND logic.
- 3. Analyze the results: Press Enter to see the dynamic array spill the filtered results instantly across the adjacent cells.
Frequently Asked Questions
How do I use OR logic instead of AND logic in the FILTER function?
To apply OR logic, use the addition operator (+) instead of the multiplication operator (*). For example: =FILTER(A2:B10, (A2:A10>100)+(B2:B10<10), ""). This will return rows where either condition is met.
Why is my FILTER formula returning a #CALC! error?
The #CALC! error usually occurs when the FILTER function finds no matching records and the [if_empty] argument is not provided. Adding "" as the third argument will return a blank cell instead of an error.
Can I filter based on text criteria as well as numbers?
Yes, you can mix text and numeric criteria. For text, enclose the criteria in quotation marks, such as (A2:A10="Completed")*(B2:B10>100).
Why does my formula treat the > or < operators as text?
This happens if you wrap the operators in quotation marks (e.g., ">100"). In dynamic array formulas like FILTER, you should write the condition directly without quotes, like (B2:B10>100).
