Back to Blog

How to use FILTER Function in Google Sheets: Everything You Need to Know

FILTER is one of the most frequently used functions in the cases covered in our blog. It was used to build a sales tracker, as well as explain other Google Sheets functions, such as IFS vs. nested IF statements. Now it’s time for FILTER to solo. Let’s spell out how it works and what you can do with this function in your data activities.

FILTER function explained

Google Sheets FILTER function filters out subsets of data from a specified data range by a provided condition. 

The FILTER function and the filter functionality are different things. The function is used within formulas to filter subsets of data.

The functionality is a button on the toolbar of a spreadsheet. It filters the entire data set.

FILTER function syntax

=FILTER(data_range,condition_1, condition_2,...)
  • data_range – a range of cells to filter. Example: A2:A
  • condition – a cell range that contains TRUE or FALSE values of the filter criteria. The filter criteria mostly contains the comparison operators (“=“, “<“, and “>“), for example, A2:A>20. However, you can also use the REGEXMATCH function in the FILTER condition.

If a cell contains the filter criteria, you can reference this cell in the FILTER condition. Example: A2:A>C1. For more about how to reference a certain data range from another sheet, read our blog post How to Link Data Between Multiple Spreadsheets.

FILTER formula example

As an example, let’s use the case introduced in the video Google Sheets FILTER – 5 tips for beginners created by Railsware. The clip itself provides basic insights into the FILTER function, so we highly recommend you watch it. 

Here is a data set that we’ll use:

In this data set, we need to filter our clients that come from the EMEA (Europe, Middle East, and Africa) region only. Here is the formula to do this:

=filter('Data set'!A2:A,'Data set'!C2:C="EMEA")
  • 'Data set'!A2:A – the Clients column to filter.
  • 'Data set'!C2:C="EMEA" – the filter condition: all "EMEA" values within the Region column.

This is the simplest FILTER formula example with only one condition. Meanwhile, you can filter out by multiple conditions, combine FILTER with other Google Sheets functions, and much more.

How to use FILTER function: case studies

To unfold the FILTER capacity, let’s use a bigger data set, which will contain different data types. We decided to run an SQL query to our database in BigQuery and import the query results into Google Sheets using Coupler.io. If you want to learn how to do this, read the blog post, BigQuery to Google Sheets Connection.

Actually, with Coupler.io, you can import data into Google Sheets from different sources, such as BigQuery, Pipedrive, Airtable, and many more. Check out the Coupler.io home page to learn which data sources are supported.

Here is the data set that we’ve exported from BigQuery:

FILTER by multiple conditions (AND logic)

Note: Google Sheets FILTER function doesn’t support mixed conditions: the specified conditions can be for either columns or rows.

FILTER by the AND logic means that the formula will return the result if all the specified conditions are met.

Formula syntax

=FILTER(data_range,condition_1, condition_2, condition_3,...)

Formula example

Let’s filter out clients ('Dataset from BigQuery'!A2:A) by the three conditions:

  • Subscription type: Squad ('Dataset from BigQuery'!B2:B="Squad")
  • Country: Poland ('Dataset from BigQuery'!C2:C="Poland")
  • Payment amount: less than $100,000 ('Dataset from BigQuery'!F2:F<100000)

Here is the formula:

=filter(
   'Dataset from BigQuery'!A2:A,
   'Dataset from BigQuery'!B2:B="Squad",
   'Dataset from BigQuery'!C2:C="Poland",
   'Dataset from BigQuery'!F2:F<100000
)

Link to the spreadsheet with the formula

FILTER by multiple conditions (OR logic)

FILTER with the OR logic means that FILTER will return the result if any or all of the specified conditions are met. 

You can use FILTER with OR logic to specify multiple conditions from the same column.

Formula syntax

=FILTER(data_range,(condition_1.1)+(condition_1.2), condition_2,(condition_3.1)+(condition_3.2),...)

Formula example

Let’s say you need to filter out clients ('Dataset from BigQuery'!A2:A) by different conditions from the same column:

  • Subscription type: Squad ('Dataset from BigQuery'!B2:B="Squad")
  • Country: Poland ('Dataset from BigQuery'!C2:C="Poland") and/or Sweden ('Dataset from BigQuery'!C2:C="Sweden"
  • Payment amount: less than $100,000 ('Dataset from BigQuery'!F2:F<100000) and/or more than $50,000 ('Dataset from BigQuery'!F2:F>50000)

Here is the formula:

=filter(
   'Dataset from BigQuery'!A2:A,
   'Dataset from BigQuery'!B2:B="Squad",
   ('Dataset from BigQuery'!C2:C="Poland")+
      ('Dataset from BigQuery'!C2:C="Sweden"),
   ('Dataset from BigQuery'!F2:F<100000)+
      ('Dataset from BigQuery'!F2:F>50000)
)

Link to the spreadsheet with the formula

FILTER formula error: no matches are found in FILTER evaluation 

The FILTER No matches error occurs when there are no values that meet the specified conditions. For example, we know that we don’t have any clients from Germany with a payment amount below $100,000. So, the following formula will return #N/A:

=filter(
   'Dataset from BigQuery'!A2:A,
   'Dataset from BigQuery'!B2:B="Squad",
   'Dataset from BigQuery'!C2:C="Germany",
   'Dataset from BigQuery'!F2:F<100000
)

To make your FILTER formula return an empty cell instead of the #N/A error, add the IFERROR function at the beginning:

=iferror(
   filter(
      'Dataset from BigQuery'!A2:A,
      'Dataset from BigQuery'!B2:B="Squad",
      'Dataset from BigQuery'!C2:C="Germany",
      'Dataset from BigQuery'!F2:F<100000
   )
)

Link to the spreadsheet with the formula

FILTER by number

Here are the conditions you can apply to filter numeric values:

Equal to (“=”) X

Let’s filter out all clients with a payment amount equal to $56,298:

=filter('Dataset from BigQuery'!A2:A,'Dataset from BigQuery'!F2:F=56298)

Greater than (“>”) X

Let’s filter out all clients with a payment amount greater than $56,298:

=filter('Dataset from BigQuery'!A2:A,'Dataset from BigQuery'!F2:F>56298)

Less than (“<“) X

Let’s filter out all clients with a payment amount less than $56,298:

=filter('Dataset from BigQuery'!A2:A,'Dataset from BigQuery'!F2:F<56298)

Not equal to X

Let’s filter out all clients with a payment amount not equal to $56,298:

=filter('Dataset from BigQuery'!A2:A,'Dataset from BigQuery'!F2:F<>56298)

Even values 

Let’s filter out all clients with even payment amounts:

=filter('Dataset from BigQuery'!A2:A,iseven('Dataset from BigQuery'!F2:F))

Odd values 

Let’s filter out all clients with odd payment amounts:

=filter('Dataset from BigQuery'!A2:A,isodd('Dataset from BigQuery'!F2:F))

Link to the spreadsheet with the formulas

FILTER by text (exact match/non-match)

The basic filter by text includes two conditions:

  • Return the values that are exactly equal to the specified text string
  • Return the values that are exactly not equal to the specified text string

For example, let’s filter out clients with the lost status:

=filter('Dataset from BigQuery'!A2:A,'Dataset from BigQuery'!E2:E="Lost")

and those that have statuses other than lost:

=filter('Dataset from BigQuery'!A2:A,'Dataset from BigQuery'!E2:E<>"Lost")

Link to the spreadsheet with the formulas

FILTER by text (partial match)

In the blog post COUNTIF vs. COUNTIFS, we explained that you can use wildcards (“?” and “*“) to check the partial match of the data range. Wild cards do not work with FILTER. However, the SEARCH, FIND, and REGEXMATCH functions nested within the FILTER formula can do the job.

Use FILTER + SEARCH to filter by the needed text with the insensitive text case

SEARCH formula syntax

=SEARCH("text_to_search", cell_range, [starting_at])
  • text_to_search – the text you need to find.
  • cell_range – the range to scan for the sought text.
  • [starting_at] – the character number to start looking from (optional parameter).

Now, let’s nest SEARCH within FILTER to filter out all clients with a double “o” (oo) in their names:

=filter('Dataset from BigQuery'!A2:A,search("oo",'Dataset from BigQuery'!A2:A))

Use FILTER + FIND to filter by the needed text with the sensitive text case 

If the text case matters for your filter criteria, nest the FIND function within the FILTER formula. FIND and SEARCH have the same formula syntax. So, let’s filter out all clients, who have “Oo” in their names.

=filter('Dataset from BigQuery'!A2:A,find("Oo",'Dataset from BigQuery'!A2:A))

Use FILTER + REGEXMATCH for an advanced filter by text

With REGEXMATCH, you can specify a regular expression to filter the data against. This function can replace both SEARCH and FIND, as well as provide additional filtering capabilities, such as filtering by multiple text conditions in the same column.  

REGEXMATCH formula syntax
=REGEXMATCH(cell_range,"regular_expression")
  • cell_range – the range to scan for the regular_expression
  • regular_expression – the sequence of characters that define the text string to filter against.

For example, let’s filter out clients, who have “Oo” and “atz” in their names:

=filter('Dataset from BigQuery'!A2:A,regexmatch('Dataset from BigQuery'!A2:A,"Oo|atz"))

Link to the spreadsheet with the formulas

FILTER by date and time

To filter values by date (full date, month, year, etc.), you’ll need to nest FILTER with additional functions. 

Note: if you simply try to use a date value as a text string in the filter criteria, you’ll get the No matches error:

=filter('Dataset from BigQuery'!A2:A,'Dataset from BigQuery'!G2:G="3/9/2020")

Let’s do this the right way. 

Filter by the exact date

Apply the DATE function within the FILTER formula as follows:

=FILTER(cell_range,cell_range=DATE(YYYY,MM,DD))

FILTER + DATE only work if your cell range doesn’t contain time units along with the date. If your date value looks like this: 3/9/2020 7:11:45, you’d better use the QUERY Google Sheets Function for filtering. With FILTER, your formula will be quite hefty and complex. For example, here’s how it looks when filtering out clients by the transaction date March 9, 2020:

=filter(
   'Dataset from BigQuery'!A2:A,
   'Dataset from BigQuery'!I2:I>=date(2020,3,9),
   'Dataset from BigQuery'!I2:I<date(2020,3,10)
)

In our data set, we have date+time values. Let’s split this column into two separate ones (for Date and Time), and check out how to filter these values. Here is the formula to use:

={"Date","Time"; arrayformula(split(G2:G," "))}

Read our dedicated blog post for more on how to split data in Google Sheets.

Now we can apply FILTER+DATE to filter out clients by the transaction date March 9, 2020:

=filter('Dataset from BigQuery'!A2:A,'Dataset from BigQuery'!I2:I=date(2020,3,9))

Use the comparison operators (“<” and “>”) to filter the results before or after the specified date.

Filter by time

A similar syntax applies when you need to filter values by time: 

=FILTER(cell_range,cell_range=TIME(HH,MM,SS))

For example, the formula to filter clients that performed the transaction before 10:46:23 is the following:

=filter(
   'Dataset from BigQuery'!A2:A,
   'Dataset from BigQuery'!J2:J<time(10,46,23)
)

Use the comparison operators (“<” and “>”) to filter the results before or after the specified time.

Filter by day/month/year

You can easily filter values by day/month/year using the respective functions (DAY, MONTH, and YEAR) and the following syntax:

=FILTER(cell_range,DAY(cell_range)=value)

For example, let’s filter out clients separately by:

  • Day of month: 4
  • Month: May
  • Year: 2020

Here is how these filter criteria will look in the three separate FILTER formulas:

Day

=filter('Dataset from BigQuery'!A2:A,day('Dataset from BigQuery'!G2:G)=4)

Month

=filter('Dataset from BigQuery'!A2:A,month('Dataset from BigQuery'!G2:G)=5)

Year

=filter('Dataset from BigQuery'!A2:A,year('Dataset from BigQuery'!G2:G)=2020)

Use the comparison operators (“<” and “>”) to filter the results before or after the specified day/month/year.

Link to the spreadsheet with the formulas

Now, let’s explore some advanced case studies of using the FILTER function.

Remove duplicates in the filter results using FILTER+UNIQUE

In the example with FILTER + REGEXMATCH, we’ve got many duplicates in the filter results. To remove duplicates, apply the UNIQUE function in your FILTER formula, as follows:

=unique(
   filter(
      'Dataset from BigQuery'!A2:A,
      regexmatch('Dataset from BigQuery'!A2:A,"Oo|atz")
   )
)  

Link to the spreadsheet with the formula

Filter out duplicates

Let’s say you need to filter out duplicates (if any) of a cell range. Unfortunately, there is no NOT UNIQUE function in Google Sheets, which could have solved this task. But here is a workaround where you need to nest FILTER with UNIQUE, ARRAYFORMULA and COUNTIF:

=FILTER(
    UNIQUE(cell_range),
      ARRAYFORMULA(
         COUNTIF(cell_range,
            UNIQUE(cell_range))>1
      )
)

For example, there are two cell ranges: one with and one without duplicates. We applied the advanced FILTER formula to check both of them:

=iferror(
   filter(
      unique(A2:A),
         arrayformula(
            countif(A2:A,
               unique(A2:A))>1)
   )
) 

and

=iferror(
   filter(
      unique(B2:B),
         arrayformula(
            countif(B2:B,
               unique(B2:B))>1)
   )
) 

Link to the spreadsheet with the formulas

Filter the data imported from another spreadsheet (FILTER + IMPORTRANGE)

If you nest FILTER with IMPORTRANGE, you’ll be able to filter the data imported from another Google Sheets document. Here is the formula syntax:

=FILTER(IMPORTRANGE("spreadsheet", "cell_range"),[condition_1, condition_2,...])
  • spreadsheet – the URL or ID of the spreadsheet to import data from. 
  • cell_range – a range of cells to query. 
  • condition – a range that contains the filter criteria.

Read our Google IMPORTRANGE Tutorial to check out the formula example and learn more about how to use IMPORTRANGE.

Filter the filtered results (FILTER + FILTER)

Formula syntax

=FILTER(FILTER(cell_range, condition),condition)

This combination lets you filter a result that has already been filtered. This sounds complicated, but here is an example. We want to filter clients by the three conditions:

  • Subscription type: Squad 
  • Country: Poland 
  • Payment amount: less than $100,000 

The filter result should be filtered by partial text match “at“. Here is how the formula looks:

=filter(
   filter(
      'Dataset from BigQuery'!A2:A,
      'Dataset from BigQuery'!B2:B="Squad",
      'Dataset from BigQuery'!C2:C="Poland",
      'Dataset from BigQuery'!F2:F<100000
   ),
   search("at",
      filter(
         'Dataset from BigQuery'!A2:A,
         'Dataset from BigQuery'!B2:B="Squad",
         'Dataset from BigQuery'!C2:C="Poland",
         'Dataset from BigQuery'!F2:F<100000
      )
   )
)

Link to the spreadsheet with the formula

Filter the top X records (FILTER + LARGE)

Formula syntax

=FILTER(cell_range,criteria_range>=LARGE(criteria_range,rank))

Let’s filter out the top 5 clients by the payment amount. Here is the formula:

=filter(
   'Dataset from BigQuery'!A2:A,
   'Dataset from BigQuery'!F2:F>=large('Dataset from BigQuery'!F2:F,5)
)

If you need to filter by the smallest values instead of largest, use the SMALL function instead of LARGE.

Link to the spreadsheet with the formula

Sum the filtered results (SUM + FILTER)

If you combine FILTER with SUM, the formula will return the sum of the filtered results. Here is the syntax:

=SUM(FILTER(cell_range, condition))

For example, here is the formula to sum the payment amount of all lost clients:

=sum(
   filter('Dataset from BigQuery'!F2:F,
   'Dataset from BigQuery'!E2:E="Lost"
   )
) 

Bonus: Filter data and add a Total row below

Let’s say we want to do the following:

  • Filter clients by two conditions: “lost” status and transaction date after Jul 1, 2020
  • Retrieve specific columns (Client Name, Subscription Type, Country, and Payment Amount)
  • Calculate the total payment amount of the lost clients
  • Place the total sum in a separate row below the filter results

Let’s go step-by-step to see how the advanced FILTER formula can do this:

Filter clients by two conditions: “lost” status and transaction date after Jul 1, 2020

=filter(
   'Dataset from BigQuery'!A2:A,
   'Dataset from BigQuery'!E2:E="Lost",
   'Dataset from BigQuery'!I2:I>date(2020,7,1)
)

Retrieve specific columns (Client Name, Subscription Type, Country, and Payment Amount)

=filter(
   {'Dataset from BigQuery'!A2:C, 'Dataset from BigQuery'!F2:F},
   'Dataset from BigQuery'!E2:E="Lost",
   'Dataset from BigQuery'!I2:I>date(2020,7,1)
)

Place a separate row for total sum below the filter results

={
   filter(
      {'Dataset from BigQuery'!A2:C,'Dataset from BigQuery'!F2:F},
      'Dataset from BigQuery'!E2:E="Lost",
      'Dataset from BigQuery'!I2:I>date(2020,7,1)
   );
   {"Total"," "," "," "}
}

Calculate the total payment amount of the filtered clients

={
   filter(
      {'Dataset from BigQuery'!A2:C,'Dataset from BigQuery'!F2:F},
      'Dataset from BigQuery'!E2:E="Lost",
      'Dataset from BigQuery'!I2:I>date(2020,7,1)
   );
   {"Total"," "," ",
       sum(
        filter('Dataset from BigQuery'!F2:F,
         'Dataset from BigQuery'!E2:E="Lost",
         'Dataset from BigQuery'!I2:I>date(2020,7,1)
       )
      )
   }
}

Link to the spreadsheet with the formula

This is a rather tricky FILTER formula. Watch this YouTube video to learn more advanced tricks with FILTER and other Google Sheets functions.

Is that it?

FILTER is a very flexible and popular Google Sheets function. You’ve read about numerous use cases above, but there are still many that we have not covered, such as the combination of FILTER and COUNT/COUNTA/COUNTIF, as well as many other functions. If you have a special interest in a certain case study, comment below and we’ll update the blog post, or even post one dedicated to your topic. Good luck with your data!

Back to Blog

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Access your data
in a simple format for free!

Start Free