Filters are a great tool to help sift through a large set of data and bring out the important ones depending on various conditions.
Unlike Excel, Google Sheets does not have an Advanced Filter utility to help us apply multiple conditions to a filter. To remedy this, the online application does have a couple of other approaches that work similarly, if not better, than the Advanced Filter.
Let’s have a look at these methods.
Note: If you are looking to set multiple filter profiles in Google Sheets, we request you to check out our How to Create Filter Views in Google Sheets guide.
How to Filter for Multiple Conditions in Google Sheets
There are multiple conditions and scenarios that we can consider in a typical dataset to filter.
So, we’ve organized it into two sections:
- Filter by Multiple Columns
- Filter in a Single Column
On top of that, Google Sheets provides us with two different ways to filter data: Using the FILTER function or the default filter feature. In this article, we will show you both.
1. Filter for Multiple Conditions in Different Columns in Google Sheets (AND Logic)
Using the Filter Function
The FILTER function is the easiest to work with here as it follows the AND logic by default (the conditions fields of the FILTER function perform AND calculations).
This makes it quite easy to input conditions from different columns. For example, let’s say we want to find all instances of Bricks in the Eastern Region.
Step 1: Open the FILTER function and add the data range. The data range should cover the entirety of the table if not more vertically (to be more dynamic).
Step 2: We have two conditions here in two different columns. Since they are text conditions, we will take advantage of the REGEXMATCH function to find these strings in the respective columns.
For the Eastern Region in Column A:
For the Bricks Product in column B:
Step 3: Close parentheses and press ENTER.
Using the Default Filter
Using the default filter of Google Sheets is another approach we can take to filter for multiple conditions. Not to mention things become quite easy when the conditions are in separate columns.
To keep things interesting, we will change our filter conditions a little bit. This time, we will try to filter all instances of Bricks that have less than 500 units in stock.
Step 1: Apply a filter over the dataset. Simply select a cell in the dataset and click on the Filter icon in the Toolbar.
Step 2: For the first condition, set the filter to only consider Bricks for the filter menu of the Product column. Click OK to apply.
Step 3: For the second condition, we have to apply a criterion from the Filter by condition section of the In Stock column. Here, you will find the “Less than or equal to” option in the drop-down menu. Select it and input the value 500.
Filter > Filter by condition > Less than or equal to > 500
Step 4: Click OK to see the results of the filter.
2. Filter for Multiple Conditions in the Same Column in Google Sheets (OR Logic)
Surprisingly, applying a filter with multiple conditions over multiple columns is simpler than doing so in a single column in Google Sheets.
To filter with multiple conditions in a single column, we have to resort to the OR logic of calculation. As for the examples, we will try to filter for only the Bricks and Planks strings of the Product column.
Let’s see how it’s done.
Using the Filter Function
To apply a filter with multiple conditions in the same column (OR logic) with the FILTER function, we will only use a single condition field of the function.
The OR calculation itself can be done either with the help of the regular expressions (thanks to the REGEXMATCH function) otherwise with the plus (+) operator.
Since we are filtering for multiple text values, REGEXMATCH and regular expressions is the way to go.
The FILTER formula is similar to the previous one but only this time, the changes come in the condition formula:
The OR symbol for regular expression is “|”.
Thus, the final formula is:
Using the Default Filter
It is in the default filter of Google Sheets where things can get a little messy when it comes to filtering for multiple conditions in a single column.
This is because the default options available cannot take more than one filter condition for a single column.
But worry not, we can easily overcome this by applying a fairly simple custom formula.
To make things even simpler, the custom formula is similar, if not the same, as what we used for the FILTER function condition.
To apply this custom formula, follow these steps:
Step 1: You can find the “Custom formula is” option at the bottom of the drop-down menu of the Filter by condition section.
Filter > Filter by condition > Custom formula is
Step 2: Here, input the filter formula. Make sure to include the equal symbol (=) before the formula.
Step 3: Click OK to apply the filter.
Learn More: How to Filter by Condition Using a Custom Formula in Google Sheets (3 Easy Examples)
That concludes our two approaches to filtering by multiple conditions in Google Sheets. These filters are dependent on logical calculations for which Google Sheets provides us with a plethora of options.
Feel free to leave any queries or advice you might have in the comments section below.