Using the FILTER() function to return specific values in a Google Sheet

Filter formula 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.

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 formula 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

Want this FREE ebook?



7 thoughts on “Using 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!

Leave a Reply

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