If you’ve read my getting-started article on the Filter function in Google Sheets, you’ll know that it’s a very powerful function when working with data in Google Sheets.
In this post, I want to share a few more advanced filter options, such as working with dates and using OR logic.
Advanced Filter Examples in Google Sheets
Example 1: Using Reference cells and Aggregation
Here’s an example using the FILTER function to aggregate (count and sum) how many values lie between two dates. I’m using the fictitious data in the template sheet with a date and associated value for that day.
In this scenario, we want to count how many values occur in December 2017 and then sum their value, using the FILTER function:
To count the values, first put 12/1/17 and 12/31/17 into two adjacent cells and then try this formula in the next cell, as follows:
=COUNT( FILTER( $A$2:$A$95, $A$2:$A$95 <= $E$2, $A$2:$A$95 >= $D$2 ))
Example 2: Use nested functions inside the Filter function
We can make the above formula more concise by nesting the MONTH function inside of the Filter function:
and it will give you the same answer of 31:
How do I sum filtered cells in Google Sheets?
Use this formula:
We’ve done two things:
i) changed the filter range to column B (but not the test conditions!) to get the values, and
ii) wrapped the filter function in a SUM formula instead of a count.
The result is now:
How can I use OR logic inside an advanced filter?
If you know the trick for this advanced filter, this is quite easy to do, but if you don’t, well…
…then it’s easy to get lost in nested OR functions inside of FILTER functions and end up stranded in the land of errors.
In this example I have 10 rows of data in columns A and B, as follows, and I want to retrieve all of the “alpha” and “gamma” values.
Rather than running two separate filter formulas, and then combining the results, we can use one formula that gives us the desired output:
We use a “+” instead of a comma to handle the OR logic.
How does this advanced filter formula work?
Consider a basic FILTER function like this one, for example, which returns only data with “alpha” in column A:
Now, let’s see how the advanced filter works.
The next step is to return all the data that matches “alpha” or matches “gamma”.
We do this by using a “+” as the boolean operator OR inside the second argument of the FILTER function, as follows:
Now, anytime either condition of the filter is satisfied, in other words an “alpha” or a “gamma” in column A is present, then that row of data is returned in our output filtered dataset.
Have you found a use for this advanced FILTER + OR formula? Any questions? Let me know in the comments below!