Using the FILTER formula in Excel is a powerful way to narrow down data based on specific criteria. Whether you're working with a small dataset or a large one, being able to filter your data effectively can save you a significant amount of time and energy. In this article, we'll explore five different ways to use the FILTER formula in Excel with multiple criteria, along with examples and explanations.
The FILTER function is a relatively new addition to the Excel family, introduced in Excel 365 and Excel 2021. It allows you to filter a range of data based on specific criteria, making it easier to extract the data you need. Before we dive into the five ways to use the FILTER formula, let's take a look at the basic syntax:
FILTER(array, include, [if_empty])
- array: The range of data you want to filter.
- include: The criteria you want to use to filter the data.
- [if_empty]: Optional argument that specifies what to return if the filter criteria don't match any data.
Now, let's move on to the five ways to use the FILTER formula in Excel with multiple criteria.
1. Filtering Data Based on Multiple Criteria Using AND Logic
When you need to filter data based on multiple criteria using AND logic, you can use the FILTER formula with the AND function. For example, let's say you have a dataset with sales data, and you want to filter the data to show only the sales that occurred in the North region and had a total value greater than $10,000.
=FILTER(A2:D10, (A2:A10="North") * (D2:D10>10000))
In this example, the FILTER formula uses the AND function to apply both criteria: the region must be "North", and the total value must be greater than $10,000.
Example: Filtering Sales Data by Region and Total Value
| Region | Product | Date | Total Value |
|---|---|---|---|
| North | A | 2022-01-01 | 12000 |
| South | B | 2022-01-02 | 8000 |
| North | C | 2022-01-03 | 15000 |
| East | A | 2022-01-04 | 9000 |
Using the FILTER formula with AND logic, you can extract the sales data that meets both criteria.
2. Filtering Data Based on Multiple Criteria Using OR Logic
When you need to filter data based on multiple criteria using OR logic, you can use the FILTER formula with the OR function. For example, let's say you have a dataset with employee data, and you want to filter the data to show only the employees who are either from the Marketing department or have a salary greater than $50,000.
=FILTER(A2:D10, (A2:A10="Marketing") + (D2:D10>50000))
In this example, the FILTER formula uses the OR function to apply both criteria: the employee must be from the Marketing department or have a salary greater than $50,000.
Example: Filtering Employee Data by Department and Salary
| Department | Employee | Date | Salary |
|---|---|---|---|
| Marketing | John | 2022-01-01 | 60000 |
| Sales | Jane | 2022-01-02 | 40000 |
| Marketing | Bob | 2022-01-03 | 70000 |
| IT | Alice | 2022-01-04 | 55000 |
Using the FILTER formula with OR logic, you can extract the employee data that meets either criteria.
3. Filtering Data Based on Multiple Criteria Using Multiple FILTER Formulas
When you need to filter data based on multiple criteria that require different logic, you can use multiple FILTER formulas. For example, let's say you have a dataset with order data, and you want to filter the data to show only the orders that were shipped to the East region and had a total value greater than $5,000, but also had a shipping date within the last 30 days.
=FILTER(FILTER(A2:D10, (A2:A10="East") * (D2:D10>5000)), (C2:C10>=TODAY()-30))
In this example, the first FILTER formula filters the data to show only the orders shipped to the East region and had a total value greater than $5,000. The second FILTER formula then filters the resulting data to show only the orders with a shipping date within the last 30 days.
Example: Filtering Order Data by Region, Total Value, and Shipping Date
| Region | Product | Shipping Date | Total Value |
|---|---|---|---|
| East | A | 2022-02-01 | 6000 |
| West | B | 2022-01-15 | 4000 |
| East | C | 2022-01-20 | 7000 |
| South | A | 2022-01-25 | 5500 |
Using multiple FILTER formulas, you can extract the order data that meets all the criteria.
4. Filtering Data Based on Multiple Criteria Using Wildcards
When you need to filter data based on multiple criteria that include wildcards, you can use the FILTER formula with the ISNUMBER and SEARCH functions. For example, let's say you have a dataset with customer data, and you want to filter the data to show only the customers who have a company name that includes the word "Inc" or "Corp".
=FILTER(A2:D10, ISNUMBER(SEARCH({"Inc", "Corp"}, A2:A10)))
In this example, the FILTER formula uses the ISNUMBER and SEARCH functions to search for the words "Inc" or "Corp" within the company name.
Example: Filtering Customer Data by Company Name
| Company Name | Customer | Date | Total Value |
|---|---|---|---|
| ABC Inc | John | 2022-01-01 | 10000 |
| DEF Corp | Jane | 2022-01-02 | 8000 |
| XYZ Ltd | Bob | 2022-01-03 | 6000 |
| ABC Inc | Alice | 2022-01-04 | 9000 |
Using the FILTER formula with wildcards, you can extract the customer data that meets the criteria.
5. Filtering Data Based on Multiple Criteria Using Dynamic Arrays
When you need to filter data based on multiple criteria that require dynamic arrays, you can use the FILTER formula with the FILTERXML function. For example, let's say you have a dataset with product data, and you want to filter the data to show only the products that have a category that includes the words "Electronics" or "Fashion".
=FILTER(A2:D10, FILTERXML("
In this example, the FILTER formula uses the FILTERXML function to create a dynamic array that includes the words "Electronics" or "Fashion".
Example: Filtering Product Data by Category
| Category | Product | Date | Total Value |
|---|---|---|---|
| Electronics | A | 2022-01-01 | 12000 |
| Fashion | B | 2022-01-02 | 9000 |
| Home Goods | C | 2022-01-03 | 6000 |
| Electronics | D | 2022-01-04 | 15000 |
Using the FILTER formula with dynamic arrays, you can extract the product data that meets the criteria.
Gallery of FILTER Formula Examples
Gallery of FILTER Formula Examples
In conclusion, the FILTER formula is a powerful tool in Excel that allows you to filter data based on multiple criteria. By using the AND and OR functions, wildcards, and dynamic arrays, you can create complex filters that meet your specific needs. Remember to use the FILTER formula with caution and test your results to ensure accuracy. With practice, you'll become proficient in using the FILTER formula to extract the data you need.