The Google Sheets FILTER function is a powerful function we can use to, well, filter our data.
Suppose we want to retrieve all values above a certain threshold? Or values that were greater than average? Or all even, or odd, values?
The Google Sheets FILTER function can easily do all of these, and more, with a single formula.
New in this post for 2018:
This video is lesson 13 of 30 from my free Google Sheets course: Advanced Formulas 30 Day Challenge.
Basic examples using the Google Sheets FILTER function
In this example, we have a range of values in column A and we want to extract specific values from that range, for example the numbers that are greater than average, or only the even numbers.
The filter formula will return only the values that satisfy the conditions we set. It takes two arguments, firstly the full range of values we want to filter and secondly the conditions we’re going to apply. The syntax is:
=FILTER("range of values", "conditions we're testing")
For the example shown in the top image, here are the conditions and corresponding formulas:
|Filter for < 50||
|Filter for > average||
|Filter for even values||
|Filter for odd values||
The results are as follows:
(Note: not all the values are shown in column A.)
Click here to get your own copy >>
Question: can you test multiple conditions inside a Google Sheets FILTER function?
For example, using the basic data above, we could display all the 200-values (i.e. values between 200 and 300) with this formula:
=FILTER(A3:A21, A3:A21>200, A3:A21<300)
Question: can you do a filter of a filter?
Yes, you can!
Use the output of your first filter as the range argument of your second filter, like this:
=FILTER( FILTER( range, conditions ), conditions )
An advanced example using the Google Sheets FILTER function
Here's another example using the FILTER function to 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 ))
which gives the following result:
But there is a better way!
We can make our formula more concise:
and it will give you the same answer of 31:
What about summing the values in column B?
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: