FILTER-ing Dynamic Arrays with Multiple Conditions

Locked lesson.

Upgrade

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.xlsx
43.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”
Login to download
  • 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.

Gift this course