Google Sheets: How to use OR inside a FILTER

Google sheets: FILTER, booleans, NOT, ISBLANK

Have you ever had a set of data in Google Sheets where you want to use FILTER to grab rows based on if a condition in one column is met or if a condition is another column is met?

Maybe you got frustrated that the OR function did not work inside the FILTER as you might have expected. Well, let me tell you matey-potatey, ‘or’ works a little differently in FILTER.

In this tutorial, we will explore how to apply alternate ‘or’ conditions in a single column or multiple columns.

NOTE: If you are looking for how to use ‘or’ in a FILTER, I am assuming you know how to use FILTER in Google Sheets.

FILTER review

Just as a refresher, FILTER works like this:

=FILTER(Display Range, filter col with condition 1, filter col with condition 2, etc...)

For example:

=FILTER(A1:D30, A1:A30 = "Goat", C1:C30 <> "Annoying", E1:E30 >10)

Here we are saying that we want to display the range from A1:D30 where column A contains the word “Goat” in the row AND column C does not contain the word “Annoying” and Column E is greater than 10 (For number of items per day that just happened to miraculously appear in goats mouth).

‘or’ in brief

The best way to explain how an ‘or’ boolean works in FILTER is with a few examples. We’ll progress through a number of examples in increasing complexity so you can reinforce how to use it. However, in short, ‘or’ is represented as a ‘+’ value in Google Sheets FILTER.

As a side note, the ‘and’ boolean is represented as an asterisk ‘*’.

Or with a single column

So for example, if I wanted to see only those rows in a single column that are equal to “condition 1” or “condition 2”, then I could represent it like this:

=FILTER(A1:C30, (C1:C30 = "Handsome")+(C1:C30 = "Goatly"))

We contain each condition in a bracket separated by the or “+” symbol. This states that if a row in column C contains the word “Handsome” or the word “Goatly” then display those rows.

OR  on different columns

Alternatively, if I wanted to see cells in one column meet a condition or the cells in another column meets another condition then I would do this:

=FILTER(A1:C8,((A1:A8 ="Goat") + (C1:C8 = "Handsome")))

This will display all rows that contain the word  “Goat” in column A and it will also display rows that contain the word “Handsome” in column C.

Time to dive into some examples:

*This is, of course, a weird example. The only animal that could truly be described as handsome in the classical sense of the term is a goat. So, the first term is perhaps redundant. 

Scenario 1: Single Column ‘or’ filter

T-Shirt Returns

So we are running this T-shirt company and we want to see the number of returns for a particular size. First, let’s take a look at the raw data:

Filtering Extra Large and Extra Extra Large T-Shirts

We want to make a list of extra large and extra extra large T-shirts so that we can see how many returns there are. We will want to display all the relevant data from column A through to C. Using our new ‘or’ approach we could do it this way:

=FILTER(A3:C34,(B3:B34 = "Extra Extra Large") + (B3:B34 ="Extra Large"))

As always, we will first need to define the range of data we want to be displayed in the first argument. Then we will start on our dual set of ‘or’ conditions in the second argument.

Why the brackets?

You might have noticed that each range and its condition are bracketed.  The brackets contain the boolean condition – TRUE or FALSE. This is saying to the FILTER function that if inside column B, there exists the cell “Extra Extra Large” then that condition is true and will assign each matching cell a value a 1, for true. 

Next, if the cells in column B contain ” Extra Large” then this condition is also true and should be marked 1 ready to be displayed.

Filtering Extra Large and Extra Extra Large T-Shirts that have been Returned

Let’s go ahead and update our FILTER function to include the condition that we want to display any “Extra Large” or “Extra Extra Large” T-Shirt that has been returned (marked true).

One option is to include another condition argument in the FILTER function:

=FILTER(A3:C34,(B3:B34 = "Extra Extra Large") + (B3:B34 ="Extra Large"),C3:C34 = TRUE)

Here we have the two alternative shirt sizes in the first condition argument:

(B3:B34 = "Extra Extra Large") + (B3:B34 ="Extra Large")

And in the second condition argument, it is saying that it also wants to only view items that are TRUE in column C.

C3:C34 = TRUE

Alternatively, we could package the two arguments, column B’s arguments, and the column C argument into one condition using the ‘and’ boolean operator ‘*’. It would look a little like this:

=FILTER(A3:C34,(B3:B34 = "Extra Extra Large") + (B3:B34 ="Extra Large") * (C3:C34 = TRUE))

Scenario 2: Multi-Column ‘or’ filter

Research Cohort Test Completion with Multiple Tests

In this example, we have a set of research subjects. Each subject is expected to take 5 tests. Each test has 3 question version sets where subjects should take at least one of these versions. Take a look at the data below:

As you can see, there are some gaps in the data where subjects did not complete any of the 3 versions of each test. Let’s use the FILTER function to look a couple of things.

Subject Completes at Least 1 Version of Test 1 Only

We’ll start off by keeping things simple. The first condition set is to filter out any subject who did not complete any version of Test 1. In more formula-like language, we are saying that only display rows that are not blank in either column C, D or E.

First, we better break things down.

To determine if a row of cells is not empty we use the ISBLANK(range) inside a NOT() function. This is what it would look like for Column C:

NOT(ISBLANK(C4:C26))

ISBLANK() takes a range or single-cell argument. It checks if the cell is blank and returns TRUE if it is. When used inside an array-based function like FILTER or ARRAYFORMULA(), ISBLANK() iterates through each cell and checks if the cell is blank.

NOT() takes a boolean TRUE/FALSE argument that can optionally be generated by a formula. If the result is TRUE inside the NOT() function, it will return FALSE.

Let’s go ahead and build the FILTER using what we know so far:

Unlike our T-shirt filter, you will notice that we have put an all-encompassing bracket around our empty cell check for column C, D and E. What we are telling the function is that if a row in:

• Column C = not blank then TRUE otherwise FALSE
• Column D = not blank then TRUE otherwise FALSE
• Column E = not blank then  TRUE otherwise FALSE

If at least one condition is true, then the row is displayed. If ALL conditions are false, then the row is not displayed.

Subjects Completed at least 1 version of Test 1 or their total score is greater than 2

Maybe we have some lazy or ‘forgiving’ researchers and they will allow any subject’s results that have at least one version of test 1 or their total score is greater than 2.

Take a look at the original data with the relevant highlighted cells.

To achieve this, we can build on our last example and simply add an ‘or’ boolean where any value is greater than 2.

We maintain our bracket around our three, Test 1 version columns and append our column R condition to the end.

Subjects must complete at least 1 version of each test

The research supervisor is taking over now and realises that they can only use the subject data for any subject who has completed at least one version of each of the five tests.

Take a look at the original data below. The red will be what will be removed with our filter.

In this example, all we need to do is to repeat the Test 1 conditions format for the three columns for each test. We will need to ensure that we put a bracket around each of the three versions for each test to contain them. Check out the formula:

You can also see that we are using the ‘and’ (*) boolean between each test block. This is saying that if there is a test result in the first, second OR third column of the test AND there is a result in the first, second OR third column of the next test…so on and so forth to Test 5, then we display the row.

Take a look at the Google Sheet:

Subjects must complete at least one version of any one test

In this final example, all we want to filter out is any subject that failed to complete all the tests. In this sample, there is only one subject 8.

Take a look at the Google Sheet:

In this scenario, we can abandon the brackets around each of the tests and put it around all of the conditions. We simply want to know if any column contains a value and if it does, then we will put it in our filter.

Take a look at the formula:

Conclusion

Using the ‘or’ boolean (+) can be a powerful tool in increasing the utility of the filter function. Remember that bracket placement is important. If you want to determine if a number of rows each may meet a criterion that will put them on the filter, then place a bracket around them as a collection.

Here is the Google Sheet for you to take a look at. Simply go to File > Make a copy, to edit your own version.

Did you enjoy the tutorial? Want to upskill and get a solid step-by-step course to become a pro at Google Sheets? Check out my course, Google Sheets: Learn the Essentials with Three Detailed Projects. Sign up today.

Want to learn more about the Google Sheets FILTER function? Check out these tutorials:

• Filtering IMPORTRANGE data in Google Sheets
• Google Sheets FILTER function: Dates and Times
• Google Sheets: Counting and Filtering Rows where Cells Contain Particular Values.
• How to Hide a Row based on a Cell Value in Google Sheets with Filter or Google Apps Script.
• How to get the most frequently appearing words in a column in Google Sheets