In this article:

Using Google Sheets FILTER Function [w/ Single & Multiple Conditions]

A simple function for filtering data with one or more conditions is FILTER. The FILTER function only requires you to specify the range of the data to be filtered and the conditions for filtering them. 

Are you ready?

Use FILTER function with a single condition

The FILTER function follows the following syntax:

=FILTER(RANGE,CONDITION1,CONDITION2,...)

Where the RANGE is the range of the dataset that you want to filter and CONDITION1 and CONDITION2 are the conditions you want to use to filter the data. 

At least one condition is required, and this is what we will show in this section.

For our example, we have the following data:

Original dataset to be filtered.
Original dataset to be filtered.

We want to filter the dataset to see who among them has an output score of more than 25. To do so, let us first specify the condition. The condition is written with the range of the column identified, then followed by a relation. For our example, we will write it as follows:

C2:C20>25

Since the entries in column C extends from C2 to C20. We will add this as the condition for our FILTER function:

=FILTER(A2:E20,C2:C20>25)

And the result looks like this:

The results after applying the filter on Google Sheets
The results after applying the filter.

In case that no entry fulfilled the conditions, the result would simply be #N/A.

Therefore, the steps for using FILTER with a single condition are easy:

Step 1. Enter FILTER function.

Step 2. Enter selected range as first argument (RANGE).

Step 3. Enter desired condition as second argument (CONDITION1).

Use FILTER function with multiple conditions, all need to be fulfilled

As specified in the previous section, you can add more than one condition for the FILTER function to follow. The additional conditions have to be separated by a comma. 

To improve upon our example, we want to add a condition to list down only entries whose quality score is more than 8.2. We can write the new function as follows:

=FILTER(A2:E20,C2:C20>25,D2:D20>8.2)

The result now looks like this:

The results after applying the filter with two conditions, both required.
The results after applying the filter with two conditions, both required.


Therefore, the steps for using FILTER with multiple conditions are similar to the ones above:

Step 1. Enter FILTER function.

Step 2. Enter selected range as first argument (RANGE).

Step 3. Enter desired condition as the next argument (CONDITION).

Step 4. Repeat step 3 for all of your conditions.

Use FILTER function with multiple conditions, at least one need to be fulfilled

For this case, you can enclose two or more conditions using the OR operator (the plus sign). For our example, we will write the function as follows:

=FILTER(A2:E20,(C2:C20>25)+(D2:D20>8.2))

The plus sign between the two conditions tell the FILTER function to list down any entry that satisfies at least one of the given conditions. 

The result now looks like this:

The results after applying the filter with two conditions, at least one required.
The results after applying the filter with two conditions, at least one required.


Therefore, here are the steps for using FILTER with multiple conditions, where at least one needs to be filled:

Step 1. Enter FILTER function.

Step 2. Enter selected range as first argument (RANGE).

Step 3. Enter desired condition as the second argument (CONDITION).

Step 4. In the same argument, add the plus symbol (+) and your other condition.

Step 5. Repeat step 4 as needed.

Use FILTER function to show the Top or Bottom 3 entries

You can narrow down the results to list down only the top 3 entries or the bottom 3 entries by using the LARGE and SMALL functions. 

Both the LARGE and SMALL functions list down the value of the nth ranking value. Their syntaxes are the following:

=LARGE(RANGE,NTH RANK)
=SMALL(RANGE,NTH RANK)

For example, if we run the following formula:

=LARGE(C2:C20,3)

It will list down the third highest value in the set. 

Therefore, we will use the LARGE and SMALL functions to extract the nth highest or smallest value in the set, and use it as a reference for comparison. 

To list down the top 3 entries for our first example, we will use the LARGE function as part of the condition:

=FILTER(A2:E20,C2:C20>=LARGE(C2:C20,3))

And the result looks like this:

The top three results filtered by FILTER function combined with LARGE function.
The top three results filtered by FILTER function combined with LARGE function.

You can do the same with the SMALL function, but be mindful of the relational operator you need to use:

=FILTER(A2:E20,C2:C20

So the result looks like this:

The bottom six results filtered by FILTER function combined with SMALL function.

... so here comes the limitations of this technique. If more than one entry contains the same value, they will all be listed together in the list. Supposedly we list the bottom three entries here, but now six entries were listed. The best work arounds for this are using the Google Sheets Slicer or Filter View.

Supercharge Your Spreadsheets with Lido

🚀 Import data from anywhere and build custom tools and powerful dashboards straight from your spreadsheet. Discover what's possible in our Build Gallery.

The sample sheet

Here is our sample sheet that you can see how the FILTER function works:


Filter Function Sample Sheet

Use our dental appointment reminders software to easily set up custom reminders from your spreadsheet in just a few clicks. 

If you enjoyed this article, you might also like our article on how to highlight duplicates in two columns in Google Sheets or our article on how to create a filter view in Google Sheets. 

If you want to learn how to send mass emails from Google Sheets, we also suggest checking out our detailed guide.

Automate repetitive tasks with Lido

Save hours on repetitive and tedious work. Lido is a new spreadsheet that connects your spreadsheets, forms, PDFs, and email inbox.

Trigger
Action
Select trigger
When a cell value in Google Sheets changes
Check Google Sheets for today's date
When a new row is added to Google Sheets
On new Google Form submission
Send me a daily reminder
On new Typeform submission
When a cell value in database changes
Check database for today's date
When a new row is added in database
When a new HubSpot customer is created
Select action
ADDCALENDAREVENT
Creates a one- or multiple-day calendar event with optional attendees
ADDHUBSPOT
Adds an object to Hubspot
ADDSLACKCHANNEL
Create a Slack channel, and optionally add a topic or members
CALLURL
Makes any HTTP request
CREATEGOOGLEDOC
Replaces [@column_name] values in a Google Doc with the corresponding table row's values
CREATEPDF
Replaces [@column_name] values in a Google Doc with the corresponding table row's values, then export it as a PDF
FETCH
Makes any HTTP request and returns the response
INSERTROWS
Inserts given array below defined values in given worksheet
SENDGMAIL
Sends an email using your Google account
SENDOUTLOOK
Sends an email using your Microsoft Outlook account
SENDSLACK
Sends a Slack message
SENDSMS
Sends an SMS message using your Twilio account
UPDATECELL
Updates cells with given values
UPDATEHUBSPOT
Updates a property of a Hubspot object
Trigger is required
Action is required
Get started