Locked lesson.
About this lesson
Using FILTER() to restrict the results of a Dynamic Array based on a single condition
Exercise files
Download the ‘before’ and ‘after’ Excel workbooks from the video tutorial and try the lesson yourself.
Single Condition DA FILTERing.xlsx48 KB Single Condition DA FILTERing - Complete.xlsx
48.5 KB
Quick reference
FILTER-ing Dynamic Arrays with a Single Condition
Working with the FILTER function to reduce the results of a dynamic array.
When to use
When you need to filter certain items out of your dynamic array results.
Instructions
Availability
- Dynamic arrays were released to Microsoft 365 AFTER Excel 2019 was released
- This means that you must have a version of Excel newer than Excel 2019 to use them
Using FILTER()
- Can be used with table components or ranges
- Syntax: =FILTER( array, include, [if_empty] )
- The array parameter can have any number of rows/columns
- The “include” formula must be a single column (row) compared to a value
- The “include” formula must have the same number of rows (columns) as the original array
Sample data
- Returning the rows from the Sales table where the Category = Beer:
- =FILTER( Sales, Sales[Category] = "Beer" )
- Returning a block of data where the value in column E = 10
- =FILTER( A10:C30, C10:C30 = 10 )
Operators for FILTER inclusion
- = Include result in array output if array item is equal to item
- < Include result in array output if array item is less than item
- > Include result in array output if array item is greater than item
- <> Include result in array output if array item is NOT equal to item
- <= Include result in array output if array item is less than or equal to item
- >= Include result in array output if array item is greater than or equal to item
- 00:04 What we're going to look at in this video is how to add single condition filters to
- 00:08 a dynamic arrays.
- 00:09 And that's where things start to get really, really powerful.
- 00:13 Now I've taken the liberty of setting up a couple of things in this
- 00:16 workbook that I want you to just recognize real quick.
- 00:18 The first one is we've got some grouping levels.
- 00:20 So I'm going to go and collapse this one on the left hand side first,
- 00:23 just to get rid of that header row.
- 00:25 So I have a little bit more room to work with, and
- 00:27 I'll play with the other ones a bit later.
- 00:29 I've also got four cells here that are light blue that have values in them.
- 00:32 And you'll notice each one of them has a drop down for a dynamic drop down list.
- 00:37 But that's the important part, they're dynamic, look, alcohol and food.
- 00:41 This one here is picking up our years,
- 00:43 which are coming from the dynamic array that's sitting in these cells.
- 00:48 How did I make that work?
- 00:50 Well, I went to set up my data validation rule through Data,
- 00:54 Data Validation, I set up a list.
- 00:56 But I couldn't refer to the unique formula that won't work.
- 01:01 So what I needed to do was actually land it using a helper column
- 01:04 in the grid first.
- 01:05 And then I can actually refer to it using the hash notation to pick up the dynamic
- 01:09 array spill range.
- 01:10 And this is unfortunate, you can't use a name range,
- 01:13 you can't use the direct formula here.
- 01:14 You have to lent the grid first and pull it in this way, but hey,
- 01:17 at least there's a way, that's the important thing.
- 01:19 We can always hide the column later.
- 01:22 So now we'll say, OK, and that's what I've done for these individual guys here.
- 01:26 The last thing I want to point out that I set up in advance was this nice little
- 01:30 category count column right here.
- 01:31 You'll notice the formula COUNTA, which is count all instances from I12 spill range.
- 01:37 So if I go and click on that, you can see that it's picking up from the cell.
- 01:40 Well, I'm going to write a formula here.
- 01:42 But right now, there's nothing there, so it says there's only one cell present.
- 01:46 So let's see what we can do.
- 01:47 Because I've got this really cool little list of categories over here.
- 01:51 But what I really want to do is I actually want to bring back the categories for
- 01:55 only alcohol items.
- 01:56 So how do I do that?
- 01:57 We use this cool little function called FILTER.
- 02:01 And what FILTER asks for,
- 02:03 it says what would you like me to actually return into your cells?
- 02:06 So I'm going to say well, I'd like to see the sales category, that's what I'm after.
- 02:11 And it says, okay, when I move to the next parameter says,
- 02:14 what would you like to include in this list?
- 02:16 Said, only like items to be included if the sales group equals alcohol.
- 02:24 And I'll just close the parentheses.
- 02:26 So what's going to happen is it's going to iterate through all of the categories,
- 02:30 it will check what the group is to see if it matches alcohol in cell I8.
- 02:34 And when I hit Enter, you'll notice that I now have 19 items are showing up.
- 02:39 The problem, Bottled Beer, Bottled Beer, Canned Beer, Canned Beer,
- 02:42 have come up twice.
- 02:44 So no big deal, let's go back and let's modify this.
- 02:48 UNIQUE, and we'll just close the parenthesis at the end.
- 02:52 And I'll be honest, this is the way that I build my arrays.
- 02:55 I will actually build the FILTER first and then I'll add the UNIQUE and
- 02:58 then I'll add the SORT then I'll add the TRANSPOSE or
- 03:00 whatever I need to do in order to make all this stuff work.
- 03:03 So layer them on gradually.
- 03:05 It's a really helpful thing because at least you can see what's happening.
- 03:07 So that's cool.
- 03:09 Let's go and see what we can do for the 2013 categories.
- 03:13 So we'll start again, we'll go FILTER, and
- 03:16 we'll choose to pick up the sales category.
- 03:20 And this time we'll say where
- 03:24 the sales years equals 2013.
- 03:29 And we'll close the parenthesis, and boom, we get a whole bunch of those.
- 03:33 We got 14 of them again, this seems like is a little bit too long.
- 03:36 So let's go back and again, wrap this in UNIQUE.
- 03:42 And now, when you hit Enter, well, we're still at 14.
- 03:46 Okay, no worries, maybe there wasn't any repeating items, at least now we know.
- 03:50 But here's the big thing that's important to recognize about this.
- 03:53 These are single column restrictions.
- 03:56 What that means, these are not 2013 alcohol categories,
- 03:59 these are 2013 categories, every single one of them.
- 04:03 That's why we got 14 of them.
- 04:04 Alcohol is restricted to seven across all years.
- 04:08 And this is actually something that's going to get kind of important to know.
- 04:11 Because if you go back and you pick up, say, I don't know 2012,
- 04:15 notice that now we have 15 categories.
- 04:17 There's something different in this one.
- 04:19 It's this one here, Liquor Modifiers.
- 04:21 If I go back to 2013 Liquor Modifiers disappears.
- 04:25 So that's kind of an important thing to be aware of.
- 04:27 And likewise, if I filter now from alcohol to food,
- 04:31 you'll see that those change nicely.
- 04:33 Now, that's the first examples that I wanted to show you.
- 04:36 What I'd like to now show you is how we can actually do something pretty amazing.
- 04:40 So I'm going to collapse this example and get it out of the way for a second.
- 04:43 And what I want to do is I want to go and
- 04:45 actually build some summary stats around this.
- 04:47 I want to know where the group is alcohol and where the year is less than or
- 04:51 equal to 2013.
- 04:52 So I'm actually working with two sort of conditions here in this case,
- 04:55 and this is a little bit tricky.
- 04:57 So what I'm going to do first is I'm going to come back and
- 05:00 I'm going to say I'd like to have my years across the top.
- 05:03 Now I am going to need a little bit of data out of what we actually have in this
- 05:07 particular area.
- 05:08 I want to refer to my years here to make this easy.
- 05:10 Let's just collapse the sales all the way.
- 05:12 So here's how it's going to go, equals TRANSPOSE, and
- 05:15 let's pick up the range here, and we get those items.
- 05:17 The problem is if I start toggling at this point,
- 05:23 less than or equal to and I hit 2012, nothing changes.
- 05:29 So I need to be a little bit smarter with this.
- 05:32 Now I'm going to do this a formula bar so we can actually read it.
- 05:34 Because it's not G12 that I actually want.
- 05:38 So let's show you how we work with ranges, FILTER,
- 05:42 we're going to filter the dynamic range.
- 05:45 Notice this the range reference.
- 05:46 And what we're going to do is we're going to say that we want to include
- 05:52 items where, the same dynamic reference, is less than or equal to 2012.
- 05:58 And close the parenthesis.
- 05:59 So, ideally this should go through and say, well 2013 is not less than or
- 06:03 equal to so forget it, don't include it.
- 06:05 And then of course,
- 06:07 we'll need to transpose it to make it go horizontally instead of vertically.
- 06:12 And at this point, we now have something that is
- 06:16 dynamically picking up our 2013 and 2012, nice.
- 06:21 For our sales, I now have two things that I want to pick up
- 06:24 because I obviously want alcohol sales for these years.
- 06:27 So, what I'm going to do at this point is I'm going to write a nice little formula
- 06:31 looks like this.
- 06:31 We're going to go some ifs because we have multiple conditions.
- 06:35 The SUM range comes first, that's going to be sales sales.
- 06:40 The criteria range is going to be our sales group,
- 06:47 which is going to be equal to alcohol.
- 06:51 And criteria range two is going to be sales years.
- 06:57 Now in this case it can be equal to again.
- 07:02 It's going to be equal to 2013.
- 07:05 Close the parenthesis.
- 07:07 I didn't spill, what's going on there?
- 07:10 You know what, why don't we try this again?
- 07:13 Why don't we add a nice little hash so it picks up the entire area, and
- 07:18 boom, just like that it works beautifully.
- 07:21 So what if I want to go even further?
- 07:22 What if I want my alcohol categories?
- 07:24 Well, we know at this point that we can do something like this.
- 07:28 Equals UNIQUE, FILTER, sales,
- 07:33 category, where the sales,
- 07:40 Group equals alcohol.
- 07:45 Close the parenthesis, close the parenthesis again.
- 07:49 And now we've got our categories down the left hand side.
- 07:52 What I'd really like to do now is populate the gult of this table,
- 07:56 which is going to be done with a sum if statement.
- 07:58 Equal SUMIFS Sales,
- 08:05 Sales again, our criteria at this point in time is going to be Sales,
- 08:11 Category, which equals Bottled beer.
- 08:14 Let's be smart the sign add the hash right off the bat, so we get the whole thing.
- 08:18 And where sales years is equal to 2013 hash,
- 08:26 close our parenthesis.
- 08:30 And just like that, we've basically built a pivot table.
- 08:35 But here's the really cool thing about this particular pivot table.
- 08:38 If we close this and open up our data range, a pivot table takes a refresh.
- 08:44 If I go 2014, and go wait a minute, something changed, 2011 change but
- 08:48 what else?
- 08:49 Well, guess what?
- 08:50 We don't have all of our data in here, 2014 boom.
- 08:55 This is what's really cool about dynamic arrays,
- 08:57 we can actually rebuild a live pivot table on the fly.
- 08:59 Some pretty neat stuff there.
Lesson notes are only available for subscribers.