Filter with multiple criteria

How to filter data with multiple criteria in Excel

To filter data with multiple criteria, you can use a formula based on the FILTER function combined with the boolean logic.

How to filter data with multiple criteria?

  1. Select cell F3.
  2. Type =FILTER(range, (criteria1_range = criteria1) * (criteria2_range = criteria2))
  3. Press Enter.
  4. The formula will return the filtered data based on the specified multiple criteria.

Example

In this example, we have a dataset containing information about products, their prices, and sales figures. The goal is to filter the data and display only the rows where the price is greater than 50, and the sales are greater than 100. If no rows meet these criteria, the formula will return a custom message, “No data”.

Instead of entering multiple ranges and criteria in pairs, you must combine all conditions into one logical array within the “include” argument. This design makes the FILTER function more flexible and versatile, as you can use math operations (like * for AND or + for OR) and complex expressions to create custom filtering rules in a single step.

Formula:

=FILTER(data,(price>50)*(sales>100),”No data”)

How to filter data with multiple criteria in Excel

Note: “data”, “price” and “sales” are named ranges and refer to B3:D9, C3:C9 and D3:D9. The result is an array that contains one record.

Explanation

Take a closer look at the formula!

FILTER(data, include, [if_empty])

  • FILTER extracts rows or columns from a range (data) based on a condition (include). The named range, data is the entire range you want to filter. In the example, data includes the Product, Price, and Sales columns.
  • The next argument (include) is the logical condition or array of TRUE/FALSE values that determines which rows to keep.
  • [if_empty]: A value to return if no rows meet the condition. Here, it is “No data”

Condition 1: (price > 50)

This condition checks if the price of each row is greater than 50. The result is an array of TRUE/FALSE values corresponding to each row in the price column. In the example: {FALSE; FALSE; TRUE; FALSE; TRUE; TRUE; FALSE} (assumes price is a vertical column with these values: {45; 30; 90; 20; 55; 62; 20}).

Condition 2: (sales > 100)

This condition checks if the sales for each row are greater than 100. The result is another array of TRUE/FALSE values based on the sales column. In the example: {FALSE; TRUE; FALSE; FALSE; FALSE; TRUE; TRUE} (assumes sales is {100; 200; 100; 300; 100; 300; 300}).

Combining Conditions: (price > 50) * (sales > 100)

The “*” operator is used to combine the two conditions. In Excel, multiplication between arrays coerces the TRUE/FALSE values into 1s and 0s.

  • price > 50 returns {0; 0; 1; 0; 1; 1; 0}
  • sales > 100 returns {0; 1; 0; 0; 0; 1; 1}
  • Their multiplication gives {0; 0; 0; 0; 0; 1; 0}.

The multiplication “*” creates an AND condition. Both price > 50 and sales > 100 must be TRUE for a row to qualify.

Workaround with the IF function

You can achieve the result without modern formulas, too. In this case, use a nested IF formula:

=IF((price > 50)*(sales > 100), data, “No data”)

The result is the same as the previous example.

Download the practice file.