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:
Conditions  Formula 
Filter for < 50 
=filter(A3:A21,A3:A21<50)

Filter for > average 
=filter(A3:A21,A3:A21>AVERAGE(A3:A21))

Filter for even values 
=filter(A3:A21,iseven(A3:A21))

Filter for odd values 
=filter(A3:A21,isodd(A3:A21))

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?
Absolutely!
For example, using the basic data above, we could display all the 200values (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:
=COUNT(FILTER($A$2:$A$95, MONTH($A$2:$A$95)=12))
and it will give you the same answer of 31:
What about summing the values in column B?
Use this formula:
=SUM(FILTER($A$2:$A$95, MONTH($A$2:$A$95)=12))
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:
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
Hi there, what would be the best way to do this in google sheets…make a list of students who have a score of 0 for at least one assignment. It has over 50 students and 15 tests listed. Showing you a super short sample below. Thanks in advance for your help.
student 1 0 1 0 5
student 2 5 6 3 2
student 3 2 0 1 8
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
I needed to do this and used regexmatch
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
I need help I need to filter numbers
{2, 34, 56, 59, 70}. For example I need to know the sum of second and first numbers sum of the third and second number the sum fourth and third number and the sum fifth and fourth number .The sum in between the numbers and added and subtracted I need a easy way to do this please added in one subtracted beside it i don’t want to take for ever doing this
Hi Ben!
Is there a way for the filter formula to return data from two different tabs on the same sheet? I am collecting communication data using a google form and my texting platform’s API, so unfortunately I cannot consolidate tabs. Still, I want to be able to see individual communication logs for each person. Does that make sense?
Thank you!
Hey Erin,
Missed this comment when you originally posted, so this is a bit late π
You’d first want to combine your data ranges from the different tabs into one data range, and then use the filter function on this combined data range.
You can combine your data ranges with curly brackets, e.g. this formula would stack data from Sheet1 columns A & B, with Sheet 2 columns A & B:
=sort({'Sheet1'!A1:B;'Sheet2'!A1:B},1,true)
You’d put this formula in a new tab, then put the filter function next to it, pointing at this data range.
Cheers,
Ben
Does this Filter feature work for multiple columns of data? I’ve been using an importrange function to import a table of data from one tab to another, turning it into a Filtered Table, and then manually filtering by specific text.
The issue is I have to manually set the filter condition and if I update the original data table then I have to revisit all subsequent tabs to update.
Hey Jasmine,
I think I get what you’re issue is… and yes, the filter function does work for multiple columns, you just add them after a comma in the formula. Here’s the syntax:
FILTER(range, condition1, condition2, ...)
And here’s an example with some filter applied to column A and D:
=FILTER(A2:B26, A2:A26 > 5, D2:D26 < 10)
Hope that helps!
Ben
Could this be used to calculate a percentage of task done. So if Column C is blank then go to Column 0 and if – count as zero if date entered count as 1 to calculate what percentage of task in Column 0 is completed?
Not sure I understand your question here… sounds like you might want to do a COUNTIF (or SUMIF if you have 0’s and 1’s) based on your condition and then divide by the total of items to get a percentage completed.
Hi Ben
If I’ve got my main data on Tab A, and I have applied a filter to see what I need to on Tab B, can I get Tab B to update automatically whenever new rows get added to Tab A (i.e. new rows will appear on Tab B automatically if I have added a row to Tab A that matches the filter)?
Thank you!
Anna
Yes, you can! When you reference Tab A in your filter, make sure you don’t specify a final row in the range, e.g. your range should look like this:
'Tab A'!A1:D
with no number at the end. That way the range extends all the way to the bottom of your sheet so new data will be included.
Cheers,
Ben
Hi, i have a sheet that contains column with names, column with dates and column with Quality Score. For example on 10/1/2017, I have 5 unique names and each names have multiple rows with survey and quality score ratings. The name John has 5 rows with quality score on all rows, the names michael and charlie both have 5 rows as well but not all rows have quality score, 3 out of 5 rows are blank, the last 2 names are Bob and Eman with 5 rows but all rows show blank on quality score. What I’m trying to do is im trying to pull the names that doesn’t have quality score at all or show blank on all rows for quality score.
Hi Zyre,
You’ll want to do something like this:
=filter(A:C , C:C<>"")
where I’m assuming your three columns are A, B and C, and that the quality score column is column C. This formula will return all the nonblank values. You can add other conditions into the filter too.
Cheers,
Ben
I am trying to filter a sheet that has 55,000 rows. I need to get that to a manageable size as most of those rows are irrelevant to my needs. The filter formula I have in place works fine, but requires me to put finite ranges(A4:A1500, etc.). It works up to 15,000 rows, but anything over that tells me no matches are found. I am changing both the search range, and condition range to the same parameters. Any Ideas???
Hey Adam,
Does it work if you put ranges like A4:A, without specifying a final row? Haven’t heard of this problem before or seen a solution.
Cheers,
Ben
Hi Ben, Great post! Is there a way to filter a column by the color of the text in each cell? Or by the background color of the cell? Or by cells with bold or italicized text? I can do this in Excel but haven’t been able to easily find a way in Sheets.
Thanks! Cammie
Hey Cammie,
Unfortunately not in Sheets. One of the features Excel has that Sheets doesn’t (yet) π
Ben
Hey! Trying to filter for forms that are expiring within 30 days, and currently using the following formula: =FILTER(Students, G:G > (today() – 30)), where Students is a named range containing all the relevant data, and G is a column with expiration dates in it. When I try to apply the filter, I get an error:
Error
FILTER has mismatched range sizes. Expected row count: 156. column count: 1. Actual row count: 1000, column count: 1.
Any tips on how to get around this?
Hi Ben,
Great content as always!
I read all the comments and I can’t find a similar question. I have to filter data from an importhtml and I want to have all the cell with the text beginning with “/market/”. My problem is that I can’t filter using an exact match as there is other text after this (and that is atually the information I need..). What can I do?
Hi Bin,
If I have range containing below values:
A
B
C
D
A
D
C
B
How should be filter function written to give this result:
A
B
C
D
A
B
C
D
Great post
But is it possible to filter some range for two values?
(e.g: filtering a table with specific date range and category of payment)
Yes! You can add more conditions into your filter formula using the same syntax, like so:
FILTER(range, condition1, [condition2, ...])
Hi Ben,
Thank you for the tutorial! I have a big google sheet and i want to make a web page were users can manually filter the database . So they will make choices from a menu and the page will give them the filtered google sheet back.
How can I implement the user response into the formulas?
I have a main sheet and I want to create another sheet in the same workbook that pulls information ie. if agent name is John on the main sheet then it will auto populate on the other sheet named John with the specific columns required ie. address, commission %, commission $, etc. Can this be done?
Hi Ben,
Thanks for your site !
I ask myself if it’s possible to make a FILTER of a SORT on more than one sheet in the same GOOGLE SHEET FILE ? I find notging about that and you’re the specialist ! Thank you.
Oh sorry i wanted to say FILTER or SORT. Thanks.
Hi Audry,
Not exactly sure what you mean, but since the filter and sort functions take ranges as inputs, you could potentially combine data ranges with the {} array notation to pass into the filter or sort, e.g.
=sort({'Group A'!A1:B;'Group B'!A1:B},1,true)
This will combine my data from Group A sheet with data from the Group B sheet and then sort the combined data. It does require that the data in A and B are the same format and same headings etc.
Cheers,
Ben
In the examples at the top of the article, why do you wrap in ArrayFormula for the last two, but not the first 2?
Good question! The last two need the ArrayFormula construction because of the inner function ISEVEN, which is applied to a whole range. Therefore I need to denote it as an ArrayFormula so it’ll apply to every cell, not just the first.
Actually, scratch that! The last two will work without array formulas. I’ll update the article.
Can i create a filter inside a filter formula? i.e. something like =filter({filter(range,condition)},condition)
Yes, you can do that!
Note: you can also have multiple conditions inside of a single filter function:
=filter(range, conditions 1, conditions 2, etc.)
Hi Ben. I really like this function! I’m may have an addon that can help you to not only filter but also connect your data.
Would like to invite you to test.
http://www.sheetgo.com
Would be awesome have your feedback.
Regards
Mariana
Hi,
I am using Google sheets to filter data based on a selection criteria.
I have multiple instances of this:
INDIRECT(“‘Data Feed (WD8)’!L1:L10000”)=’Sales Manager View’!T7)
So the user can select what they like to see (within a cell like T7) and then the data will automatically filter. The problem I have is I don’t know how to bring back ALL for multiple criteria. The dropdown has a list of business units in this example. I thought what I could do is simply get the user to type into T7. I can do this, it works, but then if I try to do it on multiple fields then it breaks (no matches are found in FILTER evaluation). So I can’t use more than once in this formula?
What am I doing wrong?
Thanks,
Dave
Hi Dave,
You can wrap your filter with an IF statement to deal with the ALL case, like this:
=IF( T7 = "All" , return all the data , FILTER(....) )
Ben
Further to above, I tried to put two symbols which haven’t come through on the post but it was meant to read:
I thought what I could do is simply get the user to type {less than more than} into T7. I can do this, it works, but then if I try to do it on multiple fields then it breaks (no matches are found in FILTER evaluation). So I canβt use more than once in this formula?
Hi Ben, great article! Filter is a core function of a lot of sheets I run; can’t live without it!
Found myself helping someone with their Form data filtering for two columns of criteria to count the number of times value X appears. Using some form validation they can pick their different criteria. Easy enough!
However, sometimes they want to see the count for one criteria and any/all for the other. A blank cell in either drop down cell returns the dreaded #N/A.
Is there an way I can tell the filter to match any criteria from the cell? An asterisk style command of sorts?
Scaled down formula:
=COUNTIF(filter(‘Form Responses 1’!J2:J, ‘Form Responses 1’!B2:B=A2, ‘Form Responses 1’!C2:C=B2), “Yes”)
I cobbled together a solution, in case anyone runs into a similar situation, by using IFS to string together my different criteria, ISBLANK for my empty dropdown scenarios, and 2 inverse ISBLANK statements for when both dropdowns are filled. This was the final result:
=IFS((AND(not(ISBLANK($A2)),not(ISBLANK($B2)))),(COUNTIF(filter(‘Form Responses 1′!$J$2:$J,’Form Responses 1′!$B2:$B=$A2,’Form Responses 1’!$C2:$C=$B2),D$1)),
ISBLANK($A2),(COUNTIF(filter(‘Form Responses 1′!$J$2:J,’Form Responses 1’!$C$2:$C=$B2),D$1)),
ISBLANK($B2),(COUNTIF(filter(‘Form Responses 1′!$J$2:J,’Form Responses 1’!$B$2:$B=$A2),D$1)))
Great to hear you got this sorted! π
Hey guys,
I have a master sheet that has 100+ different entries on it, each with a series of criteria to query (Colour, Width, Length, Height, Weight, etc…).
I have a Summary sheet where I’ve created a bunch of dropdowns to indicate which entries you would like to see, depending on the criteria selected, and I have a list per criteria that displays any corresponding entries, based on that ONE criteria.
What I am TRYING to do is have a list that feeds back only the entries that match ALL the selected criteria.
For instance –
I have selected the Colour RED, the Width 10, and the Height 50. Each of the discreet, percriteria lists returns all the entries that correspond with the query, but what I would really like is a list that tells me all the entries that are RED, have 10 Width AND 50 Height.
Any ideas on how I can get this done in Google Sheets?
I think I’m looking for the same thing as Gord. I have a Database of routes to fly (Tab 1 = Database). Columns = Airline, Flight Number, Departing ICAO, Arriving ICAO, Depart Runway, Arriving Runway, and then some other info that won’t be filtered but will show up with the results of the filtered columns.
I then on another tab have A1:A6 with the different columns from the other sheet titles. And in B1B3 I have a drop down menu to select the Airline, Departing ICAO code and Arriving ICAO Code. Then once those results show you can type in B4B6 the flight number, and Runway info you want to show based on what is showing from the filter (no drop down menu).
The problem with the filter I am using now is that when one of the B1B6 entries are empty, it is not filtering at all. All the B1B6 criteria have to be filled in for it to work. How do I get it to work if I only put in Criteria fro some of the columns im filtering for but not all of them?
Here is my Formula, basically this: =filter(range, conditions 1, conditions 2, etc.)
=FILTER(‘Database’!A2:J, ‘Database’!A2:A=B1, ‘Database’!C2:C=B2, ‘Database’!D2:D=B3, ‘Database’!B2:B=B4, ‘Database’!E2:E=B5, ‘Database’!F2:F=B6)
Database Tab Set up (Column):
Airline
Flight#
DepartICAO
ArriveICAO
DepartRwy
ArriveRwy
FlightLevel
Route
FlightPlan
FlightAwareLink
Filter Tab Set up:
A1 = Airline, B1 = drop down menu of all the Airline options
A2 = Departing ICAO, B2 = drop down menu of all the ICAO Code Options
A3 = Arrival ICAO, B3 = drop down menu of all the ICAO Code Options
A4 = Flight Number, B4 = manually type in a flight number once above is showing
A5 = Departing Runway, B5 = manually type in a runway# once above is showing
A6 = Arriving Runway, B6 = manually type in a runway# once above is showing
It won’t work if one of the above is empty.
Hi – Simple question…how do I get back to my original view after creating a few different filters?
The filters work great for seeing different important aspects of the data, but now I want to go back to the original layout before I applied these filters.
My filters are doing nothing but sorting by three of the columns to group the data differently.
I’m sure I’m overlooking an incredibly obvious thing here… !
Hi Terry,
You can dropdown all the filters you’ve applied and choose “Select all” again to show everything in that column. If you’ve filtered multiple columns you’ll need to do it for each one. Shame there’s not a global “Select all” option in the filter menu.
Alternatively, it’s sometimes quicker just to remove the filters altogether (from the toolbar above the Sheet), which will show all your data again. Then you can reapply it if you want to start over.
Note, you can also save a particular filter view if you want to return to it. That option is in the Filter menu on the toolbar.
Cheers,
Ben
Ben,
Can I use the FILTER function to remove blank cells from a range?
Currently, I have a 70cell vertical range in my spreadsheet (B66:B135) but only 11 cells are not blank. (All values in the range are numbers generated from a formula). I’m using =FILTER(B66:B135,B66:B135″”) in cell B139, but the formula isn’t giving me correct results.
What formula can I use to get a concise list of nonblank values? I’d like to do this without any user interaction at all. (Extra credit if I can sort the output to ascending values.)
Thanks,
Henry
Hi Ben,
Love your work – starting the 30 day challenge real soon. π
Quick question, which hopefully has an easy answer, though i can’t think of one at the moment, but filter is coming close.. i hope.
Situation.
Sheet 1 has multipe numbers in different rows and colums, combined with text. It’s a email template sheet where i fill in the article ids that has to come in an email. It then loads the images with =image(vlookup in the productfeed), and titles etc.
Now i would like to extract all those articles id’s (not campaign text) in a different sheet into one column. Is filter the right formula?
Much appreciated!
Best,
Berend
The difficulty I am experiencing is that I use filter (and query sometimes) and when the length of the returned data (i.e. number of rows) is less than the previous instance, the “old” data is left over. Filter does not understand the maximum length of the resulting area in order to clear out old results.
It’s probably not good form to answer your own question, but in case someone had a similar issue a found a way that helps me …
I was filtering a range of rows (8 columns wide). By using a formula of the following style, I could append enough blank rows to always overwrite any “old” data
={filter(……codes …) ; range} adds the extra rows I needed to overwrite the old data. The range has to be the same width as the filter range.
Thank you for this; saved me a lot of time!
I need some further help, however.
I have successfully filtered 1000 rows of data down to around 250. I need to make changes to the formulas of these filtered cells now before moving on but it won’t let me. Each time I change the data, it returns back to the original filtered data. How can I get around this problem?
TIA!