Locked lesson.

Upgrade

About this lesson

Using FILTER to restrict the results of a Dynamic Array based on one or more conditions.

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#)
Login to download
  • 00:04 In this video, I'd like to demonstrate the power of using dynamic arrays and
  • 00:09 filter and ifs altogether in order to build a completely dynamic report.
  • 00:14 Where I can filter what categories included and
  • 00:17 extract certain years less than or equal to a threshold that I've determined.
  • 00:22 We're going to start with our data table here and
  • 00:24 you'll notice that I've set up a couple of helper arrays in order to extract
  • 00:28 the unique list of groups as well as the unique list of years.
  • 00:32 And what I'm going to start with now is building a dynamic header for my columns.
  • 00:37 I'll start here.
  • 00:38 Recognizing, of course, that by default, an array will spill vertically and
  • 00:42 I want this one to spill horizontally.
  • 00:44 So I'm going to start by going and
  • 00:47 typing in =TRANSPOSE in order to make sure this comes out in a horizontal nature.
  • 00:52 The next thing I'm going to do is I'm going to filter this because
  • 00:57 I only want to have items from my array starting in G12.
  • 01:02 Where the items in this array aren't less than or
  • 01:07 equal to the year that I've chosen in J9.
  • 01:11 I'm not going to close the parenthesis on my filter.
  • 01:14 Close the parenthesis on my transpose in order to balance out this function.
  • 01:18 And now when I hit Enter, what I should get is 2012 and 2011.
  • 01:22 And yet, if I now go and change this little filter to 2013, boom, look at that.
  • 01:28 We've got all three years, fantastic.
  • 01:31 The next thing I'd like to do is I'd like to go and return the total sales,
  • 01:36 but only where the group is actually selected based on what I have here.
  • 01:41 So in order to do this, I actually need to look inside my data table,
  • 01:45 check the sales, and
  • 01:47 I need to sum these based on the fact they meet the multiple criteria.
  • 01:51 Each one of them being the year, the second one being the group.
  • 01:55 And this is going to require a sum ifs statement.
  • 01:58 So we're going to go with some ifs.
  • 02:01 The sum range is going to be the sales column.
  • 02:05 The first criteria range that I'm going to end up using here is I'm
  • 02:10 going to go with years.
  • 02:11 And I'm going to make this criteria equal to the header.
  • 02:15 But because I want this to actually read from the dynamic array and
  • 02:19 actually return an array of its own that matches all these years,
  • 02:23 I'm going to put in my hash mark to pick up that entire boundary.
  • 02:26 The last thing I need to do is I need to make another criteria range here where I'm
  • 02:30 going to go and grab the group.
  • 02:32 And I'm going to set this criteria equal to alcohol.
  • 02:37 I'm now going to close the parenthesis on this.
  • 02:39 And my array should spill to give me all of the sales values.
  • 02:42 And again, this should be completely dynamic.
  • 02:45 So if I change it to food, it works nicely.
  • 02:48 And if I set my years down to 2012, is going to collapse on its own as well.
  • 02:54 The next piece that I want to get to here is I want my nice listing here of all of
  • 02:58 the categories that are currently food categories.
  • 03:02 So what we're going to do is we're going to go and we're going to say,
  • 03:06 let's go and get our unique values from our filtered range.
  • 03:11 We're going to pick up the category
  • 03:16 where the group equals food.
  • 03:20 Close the parentheses on this and close the parenthesis on the unique.
  • 03:24 And at that point, we now have our unique list of individual categories
  • 03:29 that will dynamically react if I change what I want in my drop down.
  • 03:34 The last piece that I need to figure out is how do I get this sales information for
  • 03:39 just these categories?
  • 03:40 And this again is going to involve another sum ifs statement.
  • 03:44 So we're going to go with equals sum ifs.
  • 03:48 The sum range, once again, is going to be my sales.
  • 03:53 The criteria range this time is going to be equal to my category and I want to
  • 03:59 make sure that it is equal to whatever is on the current row of this array.
  • 04:05 And I'm also going to pick up where the sales
  • 04:09 years are equal to whatever the value is in this array.
  • 04:14 So now you can see where we're actually pulling these things and
  • 04:17 getting two dimensions coming into play here.
  • 04:19 When I close the parenthesis, boom, there we go.
  • 04:21 There are all of my sales values and I can quickly switch it out to see food.
  • 04:27 And I can quickly switch it out to say, show me all yours less than 2013 or
  • 04:32 even less than or equal to just 2011.
  • 04:35 So this is where all of these things start to come together to start building truly
  • 04:39 dynamic schedules, that you can use to show exactly what you want, how you want,
  • 04:44 when you want.
  • 04:45 And honestly, when you actually look at this really carefully,
  • 04:48 it takes a lot more work, but
  • 04:49 it's almost like building a pivot table that doesn't need to be refreshed.
  • 04:53 Pretty darn cool stuff.

Lesson notes are only available for subscribers.

Gift this course