In this post, I want to share a few more advanced filter options, such as working with dates and using OR logic.
If youve read my getting-started article on the Filter function in Google Sheets, youll know that its a very powerful function when working with data in Google Sheets. In this post, well take it one step further and look at more advanced logic with an OR condition.
Click here to make a copy of the advanced filter worksheet.
Advanced Filter Examples in Google Sheets
Example 1: Using Reference cells and Aggregation
Heres an example using the FILTER function to aggregate (count and sum) how many values lie between two dates. Im 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:=COUNT(FILTER($A$2:$A$95, MONTH($A$2:$A$95)=12))
and it will give you the same answer of 31:
How do I sum filtered cells in Google Sheets?
Use this formula:=SUM(FILTER($B$2:$B$95, MONTH($A$2:$A$95)=12))
Weve 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 dont, well
then its 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:=FILTER(A1:B10,(A1:A10="alpha")+(A1:A10="gamma"))
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:=FILTER(A1:B10,A1:A10="alpha")
Now, lets 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:=FILTER(A1:B10,(A1:A10="alpha")+(A1:A10="gamma"))
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.
Discussion on Stack Exchange about using AND and OR conditions in the FILTER function.
Check out my getting-started post on how to use the Google Sheets Filter function to work with data.
Have you found a use for this advanced FILTER + OR formula? Any questions? Let me know in the comments below!