How to use the FILTER function to return specific values in a Google Sheet

Filter function in Google Sheets

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:

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.

Filter function in Google Sheets

An advanced example using the FILTER function

Filter function in Google Sheets

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:

Filter function for dates

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:

Ebook thumbnail

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! 👍




41 thoughts on “How to use the FILTER function to return specific values in a Google Sheet”

  1. 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!

    1. 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

  2. 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!

  3. 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

    1. 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

  4. 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!

  5. 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 2017-02-14
    1 4.3 2017-02-17
    1 4.5 2017-02-18
    2 4.2 2017-02-14
    2 4.3 2017-02-17
    2 4.6 2017-02-18
    3 2.3 2017-02-14
    3 2.1 2017-02-17

    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

    1. 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

      1. 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 2017-02-18
        2 4.6 2017-02-18
        3 2.1 2017-02-17

        Does that make it clearer?
        Thanks!

        1. 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))
          1. 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

  6. 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

  7. 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!

    1. 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

  8. 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.

    1. 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

  9. 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?

    1. 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.

  10. 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

    1. 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

  11. 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.

    1. 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 non-blank values. You can add other conditions into the filter too.

      Cheers,
      Ben

  12. 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???

    1. 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

  13. 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

  14. 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?

  15. 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?

  16. 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

  17. 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)

    1. Yes! You can add more conditions into your filter formula using the same syntax, like so:

      FILTER(range, condition1, [condition2, ...])

  18. 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?

  19. 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?

  20. 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.

    1. 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

Leave a Reply

Your email address will not be published. Required fields are marked *