Locked lesson.

Upgrade

About this lesson

Combining multiple conditions in a single FILTER to restrict the results of a Dynamic Array.

Exercise files

Download this lesson’s related exercise files.

FILTER with AND-OR - Begin.xlsx
48.7 KB FILTER with AND-OR - Complete.xlsx
48.6 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 Office 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, I'm going to show you something cool, or we can return multiple
  • 00:08 rows and columns from a table all through one dynamic array.
  • 00:12 And then we're going to complicate it a little bit by trying to add some AND and
  • 00:16 OR filter criteria.
  • 00:17 The trick with this, unfortunately, AND an OR don't work,
  • 00:21 you actually have to resort to something called Boolean logic.
  • 00:24 Now, my goal here is to extract a subset and entire data table here
  • 00:29 with all four columns of records where the group equals alcohol and
  • 00:34 the year equals either 2011 or 2013.
  • 00:38 Of course, these are all driven by dropdowns that are all dynamic,
  • 00:41 which are built from my helper columns.
  • 00:42 Now, for right now, I'm just going to hide those and get them out of the way, so
  • 00:45 we got a little bit more space to work.
  • 00:47 And we're going to start by extracting some records here to get multiple
  • 00:51 columns back from our table.
  • 00:53 And we're going to do that using filter.
  • 00:55 And the array that I'm going to choose this time is not just a single column,
  • 01:00 I'm actually going to choose the entire Sales table, all rows, all columns.
  • 01:04 And what I'm going to do is I'm going to filter this, though,
  • 01:08 to where the group equals alcohol.
  • 01:11 I'm going to close the parenthesis on this.
  • 01:12 Now, you see some really cool where we actually get back multiple rows and
  • 01:17 columns from one dynamic array, very reigning stuff.
  • 01:20 Now, the trick is though, is that I want to further filter this down to
  • 01:25 where records are also equal to 2011 year only.
  • 01:29 I want to get rid of all these 2012, 2013 records.
  • 01:32 So normally, what you would expect is that you'd come back here and you'd say,
  • 01:35 all right, well, no problem, this is what the AND function is all about.
  • 01:38 So we'll take our sales group equals 07.
  • 01:41 And let me put this inside the parentheses where it would belong.
  • 01:46 What we'll do then is we come and we'd say,
  • 01:49 let's take it where the years equal 2011.
  • 01:52 We'll close the parenthesis on our AND, close the parenthesis on filter,
  • 01:57 hit Enter, and we get a hash value error, and this is awful.
  • 02:00 The reason being is because the AND function isn't supported inside filter.
  • 02:05 So let me show you first how we fix this, and then we'll talk about why.
  • 02:11 So what I'm going to do is I'm going to take my sales group here in 07.
  • 02:16 I'm going to wrap it in parenthesis, and believe it or not, I'm going to multiply
  • 02:21 it by our (Sales[Years]=08), also wrapped in parenthesis, okay?
  • 02:26 So (Sales[Group]=07) wrapped in parens,
  • 02:30 times (Sales[Years]=08), Enter, and look at that, it works nicely.
  • 02:36 Now, what the heck is going on here?
  • 02:38 Well, what's actually happening is we're multiplying two arrays against each other,
  • 02:43 and this is the tricky part to understand about this.
  • 02:46 So I'm just going to open this up for a second here and
  • 02:49 show you what we're actually working with.
  • 02:51 So if we take a look at the formula here, I have my Sales[@Group] Alcohol equals 07.
  • 02:58 And this comes back and returns me a value of TRUE, okay?
  • 03:02 And you can see this going all the way down the table here.
  • 03:04 If we take a look at this particular record, does food equal alcohol?
  • 03:08 It says no, so it returns FALSE, if this makes sense so far.
  • 03:12 We've got the same thing here with the Year =2011.
  • 03:15 You can see that I'm comparing my years here to the value that we have on this
  • 03:19 side, and my Years = 2013 to the value that we have in the cell, okay?
  • 03:23 Simple stuff so far.
  • 03:25 Now, here's the tricky thing though.
  • 03:28 We can convert these values into numeric constants.
  • 03:33 And we actually do that by typing negative, negative,
  • 03:36 open parenthesis around it, and then close the parenthesis at the end.
  • 03:40 And that will convert it to the numeric equivalent, TRUE = 1 and FALSE = 0.
  • 03:45 So let me do all these for a second.
  • 03:48 Let's go and grab this one.
  • 03:49 Let's see, we've got another one, this one is FALSE.
  • 03:52 So if everything works nicely, you should be able to convert it to 0.
  • 03:56 Now, the reality is you're very seldom ever going to try and use this double
  • 04:00 negative coercion in order to get this to work, this is really conceptual.
  • 04:04 If we go and we run this formula all the way down,
  • 04:07 you can see that the entire table turns into a series of 1s and 0s.
  • 04:11 So what's actually happening here is inside this array,
  • 04:14 when we look at (Sales[Group]=07), if it's TRUE, it comes back with a 1.
  • 04:20 If sales here equals the value in 08, if it's TRUE,
  • 04:23 it comes back with a 1, if it's not, it comes back with a 0.
  • 04:27 If we multiply 1 times 0, we get 0, which is equivalent to FALSE, and
  • 04:31 therefore the record does not get included.
  • 04:34 If we multiply 1 by 1, it comes back as 1, which equates to TRUE and
  • 04:38 therefore the record does get included, okay?
  • 04:40 So it's a little bit of binary math going on in this kind of stuff in order to make
  • 04:45 this work.
  • 04:46 So how do you extend this then to get an OR?
  • 04:50 Well, the answer is instead of using a multiplication, use a plus, because 1
  • 04:55 plus 0, if it's 1, it's good, if it's 0, it's not, it's not going to be included.
  • 05:00 But the trick with this, of course,
  • 05:03 is that we need to make sure that we're doing our sales group for a year,
  • 05:09 and then we have an OR clause to say it's one of these two things here.
  • 05:14 So in this point, I'm now going to go and say, well, let's go and
  • 05:19 check whether my years equal Q8, and close my parenthesis there.
  • 05:25 I'm going to go to the end of my group that I was trying to arrow over, so
  • 05:28 I didn't have that happen.
  • 05:29 I needed to close one more parenthesis.
  • 05:31 What you'll notice is that record have changed, I've now got 2011 and
  • 05:34 I've got 2013.
  • 05:35 The trick to understand about this thing here is it's all what's going on
  • 05:40 inside our OR statement now.
  • 05:42 So we can actually see that we've got our two different criteria here,
  • 05:45 I'm just going to space this out a little bit so you can see it a little bit easier.
  • 05:49 What we're actually doing is we're saying, if the sales here equals 08,
  • 05:53 that's going to equate to a 1.
  • 05:55 If the sales here equals Q8, well, it's not going to be both of those, so
  • 05:59 that would be a 0.
  • 06:00 We add those together, we get 1, which we can multiply against the value here for
  • 06:04 TRUE or FALSE.
  • 06:05 And you can actually see it in the table here.
  • 06:08 Here's our original sales group as a 1,
  • 06:11 we're going to multiply that against the addition of these two things.
  • 06:14 So in this case, it'll be 1 times 1.
  • 06:16 I've just summed the total here, so you can see the values.
  • 06:18 But this actually makes it very easy to find the records that are in this table.
  • 06:22 If it sums to 2, those records are going to be included.
  • 06:26 And you can see our group is alcohol, our year is equal to 2011, those are all true.
  • 06:30 In this case, our group is alcohol, our year is equal to 2013.
  • 06:34 So I've been able to find those.
  • 06:35 So this is the logic that goes up behind what we're actually working
  • 06:41 with here using this multiplication and addition to replicate an AND and an OR.

Lesson notes are only available for subscribers.

Gift this course