The FILTER function in Google Sheets 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 FILTER function can easily do all of these, and more, with a single formula.
It features as lesson 13 (of 30) in my free video course, Advanced Formulas 30 Day Challenge.
Basic examples using the FILTER function
In this example, we have a list of values in column A and we want to extract specific values from that list, 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 list of values we want to filter and secondly the conditions we’re going to apply. The syntax is:
=FILTER("list of values", "conditions we're testing")
For the example in the image at the top of this post, these are the conditions and corresponding formulas:
|Filter for < 50||
|Filter for > average||
|Filter for even values||
|Filter for odd values||
Note that the last two of these formulas (the even and odd values) are Array Formulas. When you've typed in the FILTER formula, you’ll need to hit Ctrl + Shift + Enter (on a PC) or Cmd + Shift + Enter (on a Mac), and Google will automatically add the
An advanced example using the FILTER function
Here's another example using the FILTER function to count and sum how many values lie between two dates. I've created some fictitious data in my sheet with a date and associated value for that day.
In this scenario, we want to count how many values occur in June and then sum the total value, using the FILTER function.
To count the values, first put 6/1/15 and 6/30/15 into two adjacent cells and then try this formula in the next cell, as follows:
It should look like this when you're typing it into the Google sheet:
But there is a better way!
We can make our formula more concise:
and it will give you the same answer: 26.
What about summing the values in column B?
Use this formula:
We've done two things, changed the filter range to column B and then wrapped the filter function in a SUM formula.
How to use an OR condition with the FILTER function
Want more spreadsheet tips like this?
This is tip #12 out of 35 in my brand new, and FREE, ebook! Simply subscribe to my newsletter below and I'll send you a copy:
So why not sign up for my rather lovely Google Sheets & Data Studio newsletter? Join my community of over 5,000 data analysts, digital marketers, educators and YOU, and get that free ebook! 👍