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

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

## 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))
```

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

## How it works

Complex `FILTER`

conditions work as follows (with some simplifications):

`FILTER`

goes row by row, checking conditions for each.- A condition evaluates to a so-called boolean value:
`true`

or`false`

. For example,`cust__revenue>=1000`

becomes`true`

. - Putting conditions in brackets allows us to treat them as numbers:
`1`

and`0`

, respectively. - You can do any arithmetic or formula operations with these numbers.
**For the OR-condition use summation; for the AND-condition – multiplication.** `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:

- Calculate the result of the conditions
`(cust__revenue>=1000)`

and`(cust__purch_num>=5)`

separately. - Sum values from #1. If at least one of them was satisfied, the sum will be greater than 0.
- Check if the customer has given a 5-star review:
`cust__feedback=5`

. - Invert #3 with
`NOT(cust__feedback=5)`

. If it was`true`

(1), it will become`false`

(0) and vice versa. - Multiply results from steps #2 and #4.
- 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.