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:


and it will give you the same answer: 26.

What about summing the values in column B?

Use this formula:


We've done two things, changed the filter range to column B and then wrapped the filter function in a SUM formula.

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?

Leave a Reply

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