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.

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




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

Leave a Reply

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