Locked lesson.
About this lesson
Using FILTER to restrict the results of a Dynamic Array based on one or more conditions.
Exercise files
Download this lesson’s related exercise files.
FILTER and 'IFS' Functions - Begin.xlsx48.1 KB FILTER and 'IFS' Functions - Complete.xlsx
51.6 KB
Quick reference
FILTER and 'IFS' Functions
Combining FILTER results with IF(S) functions
When to use
When you need to summarize data on a conditional basis
Instructions
Availability
- Dynamic arrays were released to Office 365 AFTER Excel 2019 was released
- This means that you must have Excel 2021 or newer (or Office 365) to use them
REMEMBER
- Functions like SUMIF and SUMIFS can be used to perform single-condition logic against arrays without the need for FILTER. (Don’t overcomplicate things!)
- FILTER is most useful when returning a visible subset of data to the grid for display.
- Rather than build a super mega formula to do everything at once, it is much easier to land the results of a filtered array to the worksheet, and then build your conditional function targeting the output of the array.
- Refer to the output of a dynamic array by adding the # character at the end of the cell reference
Sample data
- Assume you have a ‘Sales’ table that contains Year, Category, Item, and Profit columns
- To return a vertical list (acting as row headers) of unique items to A21 where the year = 2023:
- A21 =UNIQUE(FILTER( Sales[Item], Sales[Year] = 2023))
- To return a horizontal list (acting as column headers) of unique categories to B20 where the month = 5:
- B20 =TRANSPOSE(UNIQUE(FILTER( Sales[Category], Sales[Month] = "5" )))
Using SUMIF() to build a spilled horizontal array
- Build a SUMIF() against the table columns, but set the criteria to read from the spilled array (I.e. A21# not A21)
- To return total profit by category:
- =SUMIF( Sales[Category], A21#, Sales[Profit] )
Using SUMIFS() to build a computed (spilled) table
- Build a SUMIFS() against the table columns, but set the criteria to read from the spilled arrays (I.e. A21# and B20#)
- To return total profit by category and item:
- =SUMIFS( Sales[Profit], Sales[Category], A21# , Sales[Item], B20#)
Lesson notes are only available for subscribers.