FILTER function with OR and complex conditions

FILTER is an indispensable and powerful Google Sheets function that allows you to filter data by multiple criteria. By default, all FILTER criteria are joined by AND-condition: all must be true for the data to be returned. This post will show us how to use FILTER with OR or other complex conditions.

We will use this sample dataset of customers that we will need to filter:

This dataset is a simplified and randomly generated example; an actual table would also include their contact details and other data. We can also use QUERY in such simple cases, another powerful Google Sheets function, but it has its own use cases and limitations.

Simple Example: FILTER by Date

Let’s start with a simple example: get a list of customers who haven’t ordered in the last 60 days so we can enquire if we can help them with anything:

=FILTER(cust__all, cust__l_purchase<(TODAY()-60))

FILTER with OR Condition

In our next example, we want to provide a discount to our most loyal customers who spent more than $1,000 or who made at least five purchases:

=FILTER(cust__all, (cust__revenue>=1000) + (cust__purch_num>=5))

Below we will break down how this approach works.

FILTER with Complex Conditions

Let’s expand on the previous example: we want to contact all our loyal customers who have not left us a review or left a not-5-star one to enquire about what we can do better:

=FILTER(cust__all, ((cust__revenue>=1000) + (cust__purch_num>=5)) * NOT(cust__feedback=5))

This formula filters all loyal customers who have not left us a 5-star review (no review or fewer than five stars).

How it works

Complex FILTER conditions work as follows (with some simplifications):

1. FILTER goes row by row, checking conditions for each.
2. A condition evaluates to a so-called boolean value: true or false. For example, cust__revenue>=1000 becomes true.
3. Putting conditions in brackets allows us to treat them as numbers: 1 and 0, respectively.
4. You can do any arithmetic or formula operations with these numbers. For the OR-condition use summation; for the AND-condition – multiplication.
5. FILTER returns all rows that have non-zero results in all of their conditions.

Coming back to our examples, we achieve FILTER OR-condition by summing up sub-conditions (cust__revenue>=1000) and (cust__purch_num>=5). If at least one is satisfied for any given row, their sum will not be 0, and FILTER will return the row. Visual breakdown:

For the second example, we needed a more complex calculation. In the order of mathematical calculations:

1. Calculate the result of the conditions (cust__revenue>=1000) and (cust__purch_num>=5) separately.
2. Sum values from #1. If at least one of them was satisfied, the sum will be greater than 0.
3. Check if the customer has given a 5-star review: cust__feedback=5.
4. Invert #3 with NOT(cust__feedback=5). If it was true (1), it will become false (0) and vice versa.
5. Multiply results from steps #2 and #4.
6. If the result in step #5 is non-zero, the row is included in the output.

In this post, we’ve explored how to leverage the FILTER function in Google Sheets to handle OR and complex conditions. We’ve walked through practical examples, from simple filters to more sophisticated OR/AND logic. These techniques can greatly enhance your data analysis capabilities in Google Sheets.