Table of Contents
Google Sheets allows you to filter data with multiple conditions by using the Advanced Filter feature. This feature allows you to create a filter criteria that defines which rows will be displayed. You can specify multiple criteria and conditions, such as text, numerical values, dates and even blank cells. You can also specify whether you want the filtered data to be displayed in the same sheet or in a different one. This feature is a great way to quickly and easily filter data in Google Sheets.
You can use the following methods in Google Sheets to filter data with multiple conditions:
Method 1: Filter for Rows that Meet Multiple Conditions
=FILTER(A1:C10, A1:A10="A", C1:C10<20)
This filter returns the rows in the range A1:C10 where the value in column A is equal to “A” and the value in column C is less than 20.
Method 2: Filter for Rows that Meet One of Multiple Conditions
=FILTER(A1:C10, (A1:A10="A")+(C1:C10<20))
This filter returns the rows in the range A1:C10 where the value in column A is equal to “A” or the value in column C is less than 20.
The following example shows how to use each method in practice with the following dataset in Google Sheets:
Example 1: Filter for Rows that Meet Multiple Conditions
We can use the following formula to filter for all rows where the team is equal to “A” and the points is less than 20:
=FILTER(A1:C10, A1:A10="A", C1:C10<20)
The following screenshot shows how to use this formula in practice:
The only rows returned are the ones where the team is equal to “A” and the points is less than 20.
Example 2: Filter for Rows that Meet One of Multiple Conditions
We can use the following formula to filter for all rows where the team is equal to “A” or the points is less than 20:
=FILTER(A1:C10, (A1:A10="A")+(C1:C10<20))
The following screenshot shows how to use this formula in practice:
The only rows returned are the ones where the team is equal to “A” or the points is less than 20.
The following tutorials explain how to perform other common operations in Google Sheets: