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.
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:
Conditions  Formula 
Filter for < 50 
=filter(A2:A20,A2:A20<50)

Filter for > average 
=filter(A2:A20,A2:A20>AVERAGE(A2:A20))

Filter for even values 
=ArrayFormula(filter(A2:A20,iseven(A2:A20)))

Filter for odd values 
=ArrayFormula(filter(A2:A20,isodd(A2:A20)))

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 ArrayFormula()
wrapper.
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:
6/1/15  6/30/15 
=count(filter($A$2:$A$448,$A$2:$A$448<=$E$2,$A$2:$A$448>=$D$2))

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:
=count(filter($A$2:$A$448,month($A$2:$A$448)=6))
and it will give you the same answer: 26.
What about summing the values in column B?
Use this formula:
=SUM(filter($B$2:$B$448,month($A$2:$A$448)=6))
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
Check out this post on how to do this.
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:
Good stuff. Thanks for this. Saved me a lot of time.
Do you know if there is a way to return the values horizontally instead of vertically? For example if there were 4 results and the formula was in cell B2 then the results would be in B2, B3, B4, B5. Is there a way to instead have the results return in cells B2, C2, D2, E2?
Thanks!
Hey Burton,
Just wrap your formula with this one:
TRANSPOSE()
and that will flip from vertical to horizontal or vice versa.e.g.
=ArrayFormula(transpose(filter(A2:A20,iseven(A2:A20))))
Hope that helps.
Cheers,
Ben
I really like this function. Query is other that is really interesant forme because you can do pivot tables. Both are awesome! some days ago I test an addon for google that allow you import filtered tabs (importsheet.com). Its great.
Rewards!
Hi,
How do I create an exact match using filter function?
For example: I would like to have food and Food to match exactly.
food 10
Food 12
Hey Christina,
If you want to match a condition exactly, then your formula will be:
=filter(A1:B10,A1:A10="food 10")
assuming your range is in A1 to B10, with the search values (the filter values) in column A.
If you wanted to modify this to be “food 10” or “Food 12”, then your formula becomes:
=filter(A1:B10,(A1:A10="food 10")+(A1:A10="Food 12"))
Hope that helps!
Ben
Hi Ben,
Would it be possible to use the filter function to create a list of unique values using data from multiple sheets that meet a criteria found in another column on those sheets? For example, I want to create a list of all the times of day when only a specific kind of workshop was given and this data is spread across multiple sheets.
Thanks!
Hi Ben,
Would filters be appropriate to return only the last row for a set of observations for several samples?
sample, value, date
1 4.5 20170214
1 4.3 20170217
1 4.5 20170218
2 4.2 20170214
2 4.3 20170217
2 4.6 20170218
3 2.3 20170214
3 2.1 20170217
…
I really just want to be able to see the last observation row for each sample from ColA. Sometimes, the value happens to be the largest for that sample, but not always. Also, some dates might not have an observation for a particular sample, so dates don’t seem to be a reliable thing to use either.
Thanks!
Romeo
Hey Romeo,
Yes, you can use the filter function to achieve this. Taking your dataset and assuming it’s in cells A1:C9 (although can be as many rows as you want) then you can use this formula to retrieve only the last row of data (specifically the last row with a value in column A. Also this formula assumes no gaps in the values in column A):
=filter(A2:C,(A2:A<>"")*(A3:A=""))
Just make sure not to put this formula in columns A, B or C since they have your data in. Hope that helps.
Cheers,
Ben
Thanks for the quick reply!
This doesn’t work for me (but it DOES perfectly give me the last row). I’m not sure if I was clear:
I want the last row for EACH sample number, so my desired result from the data above would be:
sample, value, date
1 4.5 20170218
2 4.6 20170218
3 2.1 20170217
Does that make it clearer?
Thanks!
Ah, I see what you mean now. Thankfully an easy fix 🙂
Try modifying the formula to this:
=filter(A2:C,(A2:A<>"")*(A2:A<>A3:A))
Yep! That’s awesome!
I have been struggling with this for several months…it’s a hard question to even ask, it seems.
Thanks a million,
Romeo
Great! Happy to help.
Cheers, Ben