In this post, I want to share a few more advanced filter options, such as working with dates and using OR logic.

If y'all've read my getting-started commodity on the Filter function in Google Sheets, you'll know that information technology's a very powerful function when working with information in Google Sheets. In this post, we'll take it i step further and look at more advanced logic with an OR condition.

Click here to make a copy of the avant-garde filter worksheet.

Advanced Filter Examples in Google Sheets

Example ane: Using Reference cells and Aggregation

Here'southward an example using the FILTER function to aggregate (count and sum) how many values lie between two dates. I'yard using the fictitious data in the template sheet with a appointment and associated value for that mean solar day.

In this scenario, we want to count how many values occur in December 2017 and then sum their value, using the FILTER function:

Advanced Filter function in Google Sheets with dates

To count the values, first put 12/1/17 and 12/31/17 into ii adjacent cells and and so endeavour this formula in the next prison cell, as follows:

=COUNT( FILTER( $A$2:$A$95, $A$ii:$A$95 <= $East$2, $A$2:$A$95 >= $D$ii ))

Example two: Use nested functions inside the Filter function

Nosotros tin can make the above formula more concise by nesting the MONTH function inside of the Filter part:

=COUNT(FILTER($A$two:$A$95, Month($A$ii:$A$95)=12))

and it will give y'all the same answer of 31:

Google Sheets filter function with month

How do I sum filtered cells in Google Sheets?

Use this formula:

=SUM(FILTER($B$two:$B$95, MONTH($A$2:$A$95)=12))

Nosotros've done two things:

i) changed the filter range to column B (but not the examination conditions!) to go the values, and

ii) wrapped the filter function in a SUM formula instead of a count.

The effect is now:

Google Sheets Filter function with sum

How can I apply OR logic within an advanced filter?

If you lot know the trick for this avant-garde filter, this is quite piece of cake to do, but if you lot don't, well…

…so it's easy to get lost in nested OR functions within of FILTER functions and end upwards stranded in the land of errors.

Advanced filter with or condition

In this instance I take 10 rows of data in columns A and B, as follows, and I want to call back all of the "alpha" and "gamma" values.

Rather than running two carve up filter formulas, and and so 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 similar this one, for case, which returns only data with "alpha" in column A:

=FILTER(A1:B10,A1:A10="alpha")

At present, let's run into how the advanced filter works.

The next pace is to return all the information that matches "blastoff" or matches "gamma".

Nosotros do this by using a "+" as the boolean operator OR inside the second statement of the FILTER part, equally follows:

=FILTER(A1:B10,(A1:A10="blastoff")+(A1:A10="gamma"))

Now, someday either condition of the filter is satisfied, in other words an "alpha" or a "gamma" in column A is present, and so that row of information is returned in our output filtered dataset.

Other resource

Give-and-take on Stack Substitution about using AND and OR conditions in the FILTER function.

Cheque out my getting-started post on how to use the Google Sheets Filter function to work with information.

Have you institute a use for this advanced FILTER + OR formula? Any questions? Let me know in the comments beneath!