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 in helper columns first. 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 the MONTH function. When I provide the full set of dates to MONTH in a range, we get back a dynamic array with a month number for each date. 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. Now, since we want to filter on both Blue and June, we want to use AND logic, and AND logic requires multiplication. 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. For array, we use the full set of data. For the include argument, we use our helper column in column K. When I enter the formula, FILTER returns details for orders in June with a color of Blue. Notice these results are dynamic. If I temporarily change a color in June, results update immediately. Now to move this into an all-in-one formula, I need to recreate the same logic inside the lookup array argument. To do that, I use the same expressions we used in columns I and J, surrounded by parentheses, and multiplied together. When I enter the formula, we get the same result. And, if I check the include argument with F9, we see exactly the same array we have in column K. I can now delete the helper columns, and the all-in-one formula keeps working.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
