Locked lesson.
About this lesson
Combining multiple conditions in a single FILTER() to restrict the results of a Dynamic Array
Exercise files
Download the ‘before’ and ‘after’ Excel workbooks from the video tutorial and try the lesson yourself.
Multi-Condition DA FILTERing.xlsx43.1 KB Multi-Condition DA FILTERing - Complete.xlsx
43.3 KB
Quick reference
FILTER-ing Dynamic Arrays with Multiple Conditions
Working with the FILTER function to reduce the results of a dynamic array based on multiple conditions.
When to use
When you need to filter certain items out of your dynamic array results based on multiple conditions.
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
Filtering on a Single Condition
- 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 AND and OR in Dynamic Array Filtering
- * Used to denote “And”
- + Used to denote “Or”
- Remember:
- The “include” parameter of the filter must be wrapped in parenthesis before using a * or + character
- If you include multiple OR tests using +, as well as an AND test using *, you must also wrap all the OR tests in a surrounding set of parenthesis as well
Filtering on Multiple Conditions
- Returning the rows from the Sales table where the Category = Beer OR Wine:
- =FILTER( Sales, ( Sales[Category] = "Beer" ) + ( Sales[Category] = "Wine" ) )
- I.e. wrap each “include” test in parenthesis before joining them with +
- Returning the rows from the Sales table where the Category = Beer AND Year = 2020:
- =FILTER( Sales, ( Sales[Category] = "Beer" ) * ( Sales[Year] = 2020 ) )
- I.e. wrap each “include” test in parenthesis before joining them with +
- Returning the rows from the Sales table where the Category = Beer OR Wine, AND Year = 2020:
- =FILTER( Sales, ( ( Sales[Category] = "Beer" ) + ( Sales[Category] = "Wine" ) ) * ( Sales[Year] = 2020 ) )
- I.e. Failure to wrap the OR structure for the Beer and Wine tests in an additional set of parenthesis would result in “Category = Beer” OR “Category = Wine and Year = 2020”
- 00:04 In this video, we're going to again look at dynamic arrays, but
- 00:07 this time with a bigger, better filter.
- 00:10 We're going to filter on multiple conditions.
- 00:12 You're going to see something really interesting here.
- 00:15 To do that, though, let me just collapse this header and get it out of the way so
- 00:18 we can focus on the entire sheet.
- 00:19 I want you to see what I'm really after here.
- 00:22 We still have a nice little sales table over here.
- 00:24 We'll just verify that name.
- 00:25 There it is, that's cool.
- 00:27 And I've got a couple of helper columns that I've built here.
- 00:30 So I've got a unique sales group.
- 00:31 It's in a dynamic array that spilled down to rows.
- 00:34 And my unique sales that have been sorted in reverse order and again,
- 00:38 spilled down using a dynamic array.
- 00:41 These two little helper columns have helped me drive my data validation lists
- 00:45 for food and alcohol, as well as my years for these individual components as well,
- 00:51 where I'm saying what I'd like to actually go and pick up.
- 00:54 What I would like to do is I would actually like to fill out.
- 00:57 I'd like to get a shortlist table of everything that's on the left-hand
- 01:02 side here.
- 01:02 And I want to write one formula to get it all.
- 01:06 Can we do it?
- 01:07 Well, let's check it out.
- 01:09 Here's the first thing that's really, really cool.
- 01:11 We already know that we can work with filter on certain things.
- 01:14 But what we might not recognize,
- 01:15 you can actually return multiple columns from a filter.
- 01:17 So let's try this.
- 01:18 Let's go FILTER, and what we'll filter is we will filter the sales table.
- 01:25 I'm not going to put a column on this.
- 01:27 And what I'm going to do is I'm now going to say I'd like to filter this where
- 01:32 the sales group, Is equal to food.
- 01:40 The important thing about this is that I haven't told it to filter one column.
- 01:44 I've told it to filter the entire table.
- 01:47 And by doing that, what I've actually told it is when you find something where
- 01:52 the sales group equals J7, bring me back every single column from that table.
- 01:56 Check it out, boom, how about that?
- 02:00 So that's kind of neat.
- 02:02 But now, well, that's kind of a lot of records, actually.
- 02:06 So when I scroll down, it's not the entire table because I'm only bringing back
- 02:09 the food group, so that's good.
- 02:11 But I actually want to restrict this just a little bit further.
- 02:14 I'd like to restrict it to where the year is also equal to 2011.
- 02:19 Let's start with that first.
- 02:20 So my natural inclination at this point in time would be to go and say,
- 02:24 okay, well, maybe I can go and hit comma to add another filter.
- 02:28 But what you'll notice is that when I do that, it takes me into the next parameter,
- 02:32 the function, so that's not really ideal.
- 02:34 The next thing that I would think is I know I've got a really cool logic function
- 02:38 that I've used before for doing multi-condition logic, it's called AND.
- 02:42 So let's try this.
- 02:44 Let's go AND(Sales[Group]=J7.
- 02:49 We want to put in a comma because we want to put in our AND.
- 02:51 We'll go Sales[Years]=2011.
- 02:58 We'll close the parenthesis on the AND, and
- 03:00 then close the parenthesis on the FILTER, and you get back a VALUE error.
- 03:05 And this is actually really unfortunate is that the AND and OR
- 03:09 functions cannot be used inside a dynamic array.
- 03:12 It's a little bit mind blowing.
- 03:14 So let me show you the syntax that does work.
- 03:17 I'm going to get rid of AND.
- 03:20 And what I'm going to do is I'm now going to replace the comma that I would
- 03:24 have usually used for this with closed parenthesis, asterisk, open parenthesis.
- 03:29 Now, let me just go back and start this one from the beginning again here.
- 03:33 We have filter our sales table, and then we're saying (Sales[Group]=J7).
- 03:40 That's our first filter.
- 03:41 We wrap that filter in parenthesis and then we put in the asterisk to
- 03:46 multiply it by (Sales[Years]=J8), which is also in parenthesis.
- 03:51 You can look at this multiplier as the AND symbol.
- 03:55 And what you'll see now is that this adds another filter to our setup.
- 03:59 So we now have food sales, where the group is food and the year equals 2011.
- 04:05 So it's kind of a weird thing to have to do, but it works beautifully.
- 04:09 The next part, though, is that I want to see where the years is, 2011 or 2013.
- 04:15 So can I do that?
- 04:17 Well, sure I can.
- 04:18 We just need to know how.
- 04:20 If I want to see something that was AND 2013, well,
- 04:24 there's not going to be anything where the years are 2011 and 2013.
- 04:28 So that would actually blow things up.
- 04:29 But if I wanted to do that, I would come back and just do something like this.
- 04:33 I would come back and say (Sales[Years]= this,
- 04:38 and then close the parentheses around that.
- 04:42 Again, remember, you'll always want to close the parenthesis around each filter
- 04:45 argument that you're adding.
- 04:46 But obviously, this won't work.
- 04:48 I mean, we're not going to have anything where the year is 2011 and 2013, so
- 04:53 we need a different symbol for OR.
- 04:55 That symbol is the plus symbol.
- 04:58 But again, this isn't going to give us exactly what we're looking.
- 05:01 Because we're trying to say, I want the group is equal to food,
- 05:06 and the year is equal to either 2011 or 2013.
- 05:10 When I hit Enter on this one right now,
- 05:13 what we actually get is give me the group is equal to food and the year 2011.
- 05:18 Or just give me anything where the year is 2013, so
- 05:21 it actually removes our food filter altogether.
- 05:23 Because it says, hey, these guys here, they're 2013.
- 05:25 You said you want these two items or you want 2013.
- 05:29 And that's not exactly what we're after either.
- 05:32 The challenge in this particular case is that when you use your AND
- 05:35 symbol, like this, it is a multiplier that's going between two arrays.
- 05:40 And therefore, multiplication happens before addition.
- 05:42 So that's a bit of a challenge, easy to fix, though.
- 05:46 We're just going to go and
- 05:47 wrap one more set of parenthesis around our plus clause here.
- 05:52 And when we do this, what it's now going to say is,
- 05:56 give me (Sales[Years]=J8) or (Sales[Years]=L8) and (Sales[Group]=J7).
- 06:04 And at this point, when I hit Enter, I now get a beautiful table that's all set up.
- 06:08 It's a little bit weird.
- 06:09 It's just some things you gotta watch out for.
- 06:11 The star is the AND, plus is OR, but
- 06:13 don't forget that you need to wrap each of those arguments in a set of parentheses.
- 06:17 And if you're making multiple OR clauses, wrap the entire set of OR
- 06:20 clauses in its own set of parentheses.
- 06:22 And at that point, you'll be good.
Lesson notes are only available for subscribers.