FILTER with boolean logic

In this worksheet we have some sample order data in a table called “data”.

Let’s use the FILTER function to find all “blue” orders in June.

To visualize how this works I’m going to set up the logic inhelper columnsfirst.

Then, I’ll move that logic into the FILTER function, to make an all-in-one formula.

First, we’ll test for dates in June with theMONTH function.

Because we only want dates in June, I need to compare this result to the number 6.

Now we get TRUE for all dates in June, and FALSE for all other dates.

Next, I’ll test the colors for “blue”.

This is a simple expression that compares values in the color column to the text “blue”.

This returns TRUE for orders where the color is Blue, and FALSE for everything else.

When I multiply the helper columns together, we get an array of 1s and 0s.

The 1s correspond to orders that meet criteria.

This array will become the lookup array inside FILTER.

Now let’s configure the FILTER function.

Forarray, we use the full set of data.

Notice these results are dynamic.

If I temporarily change a color in June, results update immediately.

When I enter the formula, we get the same result.