Tutorial Notes: Using Excel's FILTER Function with Multiple Criteria

Objective

• Learn how to use Excel's FILTER function to filter data based on multiple criteria across one or more columns.
• Cover both "AND" and "OR" logic.
• Learn to extract only the columns needed using CHOOSECOLS and sort using SORT.

---

Dataset Example

• Table named TableDiv with columns:
  • Division: Utility, Productivity, Game
  • Name
  • Department: e.g., Finance, Sales
  • Yearly Salary

---

Step-by-Step Guide

1. Basic FILTER Usage (Single Condition)

• Syntax:

  =FILTER(array, include, [if_empty])
  

• Example: Filter all rows where Division = "Productivity"

  =FILTER(TableDiv, TableDiv[Division] = "Productivity")
  

2. Adding Multiple Criteria (AND Condition)

• Goal: E.g., Return people in Productivity division AND Finance department.

• How:

  • Enclose conditions in parentheses.
  • Use * (multiplication) for "AND".

• Example:

  =FILTER(TableDiv, 
          (TableDiv[Division]="Productivity") * (TableDiv[Department]="Finance"))
  

  • Explanation: Multiplying Boolean arrays converts TRUE to 1 and FALSE to 0; only rows where both conditions are TRUE return 1.

• Adding More Criteria:

  • E.g., Yearly Salary > 100,000:

    =FILTER(TableDiv, 
            (TableDiv[Division]="Productivity") * (TableDiv[Department]="Finance") * (TableDiv[Yearly Salary]>100000))
    

3. Using the "if_empty" Parameter

• Third argument returns specified value if no data matches filter.
• Use "" (empty string) to return nothing instead of error.

4. Using OR Logic

• Goal: E.g., Division = Productivity OR Department = Finance.
• How:
  • Use + (addition) for "OR".
• Example:

  =FILTER(TableDiv, 
          (TableDiv[Division]="Productivity") + (TableDiv[Department]="Finance"))
  

  • Any non-zero result (1 or 2) returns TRUE and is included.

5. Filtering Specific Columns (CHOOSECOLS)

• Goal: Return only specific columns (e.g., Name and Yearly Salary).
• How:
  • Use CHOOSECOLS(array, col_num1, col_num2, ...)
• Example:

  =CHOOSECOLS(FILTER(TableDiv, (TableDiv[Division]="Productivity")*(TableDiv[Department]="Finance")), 2, 4)
  

  • Here, columns 2 and 4 correspond to Name and Yearly Salary.

6. Sorting Results (SORT)

• Goal: Sort filtered data by salary in descending order.
• How:
  • Use SORT(array, sort_index, sort_order)
• Example:

  =SORT(
    CHOOSECOLS(FILTER(TableDiv, ...), 2, 4), 
    2, 
    -1
  )
  

  • Sorts by second column (Yearly Salary) in descending order.

7. Multiple "OR" Criteria in Same Column

• Goal: Return rows where Division is "Game" OR "Utility".
• How:
  • Use + ("OR") and brackets for each criterion.
• Example:

  =FILTER(TableDiv, (TableDiv[Division]="Game") + (TableDiv[Division]="Utility"))
  

  • Note: Brackets are required around each condition.

8. Combining AND with OR Logic

• Goal: E.g., Division is "Utility" or "Game" AND Salary > 80,000.
• How:
  • Group OR conditions, then multiply by the AND condition.
• Example:

  =FILTER(TableDiv, ((TableDiv[Division]="Utility") + (TableDiv[Division]="Game")) * (TableDiv[Yearly Salary]>80000))
  

  • Ensure brackets are used appropriately for correct logic.

---

Tips & Common Pitfalls

• Always use parentheses around each condition.
• Use * for AND logic, + for OR logic in the include part.
• Use the if_empty parameter for custom empty return.
• To retrieve specific columns after filtering, wrap with CHOOSECOLS.
• Sort final results with SORT if needed.

---

Practice Challenge

• Task: From the TableDiv data, return rows where Division is "Game" or "Utility" AND Yearly Salary > 80,000.

  Solution:

  =FILTER(TableDiv, 
    ((TableDiv[Division]="Utility") + (TableDiv[Division]="Game")) * (TableDiv[Yearly Salary]>80000))
  

---

Conclusion

• The FILTER function is powerful for extracting filtered data based on complex, multiple criteria.
• Combine CHOOSECOLS and SORT for tailored and organized results.
• Mind your use of parentheses for correct formula logic!
• Experiment with your own conditions and datasets.

---

If you found this helpful, consider subscribing for more tutorials!