How to Filter with the OR Condition in Google Sheets (3 Easy Ways)

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)?

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.

`(C2:C22>=900)`

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:

`+(C2:C22<500)`

Step 4: Close parentheses and press ENTER.

`=FILTER(A2:C22,(C2:C22>=900)+(C2:C22<500))`

Read More: Filter Entries if it Does Not Contain Value in Google Sheets (2 Easy Ways)

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:

`=FILTER(A2:C22,(C2:C22>=900)+(B2:B22="Bricks"))`

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:

`REGEXMATCH(B2:B22,"Bricks|Planks")`

The final formula with FILTER:

`=FILTER(A2:C22,REGEXMATCH(B2:B22,"Bricks|Planks"))`

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:

`=FILTER(A2:C22,(REGEXMATCH(B2:B22,"Bricks"))+(REGEXMATCH(A2:A22,"East")))`

Read More: Applying Filter with REGEXMATCH Function in Google Sheets (Easy Examples)

Final Words

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

Mehrab Imtiaz

Mehrab Imtiaz is a Technical Writer for officewheel.com specializing in everything Google Sheets. Mehrab has always had a profound passion for writing and with his experience with MS Excel and Google Sheets throughout his career, it seems to be a great match when it comes to dishing out tips and tutorials for all to enjoy.

We will be happy to hear your thoughts