Filtering with multiple criteria is a common requirement for most spreadsheets. However, doing so gives rise to logical calculations of how these conditions will behave. For example, do we want filter conditions that are independent of the others (OR logic), or do we want all the conditions dependent on each other (AND logic)?
The logical condition we will discuss in this article is the OR condition used to filter in Google Sheets.
Let’s get started.
3 Ways to Filter with the OR Condition in Google Sheets
For the following examples, we will filter data by using the FILTER function.
The Filter function syntax:
FILTER(range, condition1, [condition2, ...])
The conditions that will be applied in the condition fields of the function can be utilized as custom formulas for the default filter of Google Sheets.
Typically, we’d use the different condition fields of the FILTER function to apply multiple conditions. However, that would set up the filter for the AND logic. To apply the OR logical condition, we have to take advantage of certain symbols and functions.
1. Using the Plus Symbol (+) to Set the OR Condition in the Filter (Single Column)
Consider the following worksheet:
For our example, we will use this dataset to filter entries that have an “In Stock” value greater than or equal to 900 or less than 500 units.
Step 1: Open the filter function at the desired cell. Input the data range of the filter.
Step 2: Apply the first condition for the In Stock column in the condition field.
This first condition looks for all values in the In Stock column that are 900 and above. The condition is enclosed in parentheses as it is a single Boolean condition and another will be added alongside it.
Step 3: Input a plus symbol (+) after the first condition. This represents the OR condition for the filter. Then, add the second condition:
Step 4: Close parentheses and press ENTER.
2. Setting the OR condition Filter to Multiple Columns in Google Sheets
Applying filter conditions for values in multiple columns does not bring about much change in the formula that we’ve just discussed. Only the condition’s data range will change.
For example, if we are to filter entries that have 900 or above units in stock or are Bricks in the Product column, the formula will be:
3. How to Apply the OR Condition for Partial Text Filter in Google Sheets (FILTER and REGEXMATCH)
To apply a partial text match filter in Google Sheets, we must take advantage of the regular expression function called REGEXMATCH.
Not only for partial matches, but regular expressions also allow users to apply logical calculations with text strings. That includes the OR logical condition.
In regular expressions, the OR condition is represented by the “vertical bar” symbol (|). The key is just above or beside the ENTER key of the keyboard.
As for our example, let’s say we want to filter entries that have the string “Bricks” or “Planks” in the Product column.
The text lookup condition will be something like this:
The final formula with FILTER:
And as for using multiple columns to filter with the OR condition and partial text in Google Sheets, we must use the plus (+) Boolean approach.
For example, if we want entries with either the Product Bricks or the Region East, the formula will be:
All the examples shown above worked with two conditions following the OR logic. Understand that you can add more conditions using the same methods.
With that, we conclude all the ways we can use to filter with the OR condition in Google Sheets.
Feel free to leave any queries or advice you might have for us in the comments section below.
Related Articles for Reading
- How to Filter Based on a Cell Value in Google Sheets (2 Easy ways)
- Filter Entries if it Does Not Contain Value in Google Sheets (2 Easy Ways)
- Filter Values that Contains Multiple Text Criteria in Google Sheets (2 Easy Ways)
- How to Set a Filter in Google Sheets (An Easy Guide)
- How to Create Filter Views in Google Sheets (An Easy Guide)