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:

Sample dataset: customer name, total revenue, number of purchases, latest purchase, feedback
Sample dataset

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.

We will use named ranges and best naming practices to work with this data much easier. Read more about the advantages of named ranges in Google Sheets.

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))
Results of FILTER by date
Results of FILTER by date

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))
Results of FILTER with OR condition
Results of FILTER with OR condition

These conditions require round brackets () around them; the formula will not work without them.

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

Results of FILTER with complex OR and AND conditions
Results of FILTER with complex OR and AND conditions

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:

Breakdown of the intermediary values in FILTER with OR condition
Breakdown of the intermediary values in FILTER with OR condition

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.
Breakdown of the intermediary values in FILTER with complex conditions
Breakdown of the intermediary values in FILTER with complex conditions

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.