3 Ways To Use Multiple Criteria In Google Sheets QUERY Function (Examples)

Can you use multiple criteria to filter data in Google Sheets using the QUERY function?

Within the query parameter of the QUERY function the WHERE clause enables users to filter data based on multiple criteria. The three types of logical operators permitted when combining multiple criteria are AND, OR and NOT.

Here are some examples demonstrating each of the logical operators, and to assist in demonstrating how these operators work, here is a sheet labelled Data which will be used as the basis for performing the QUERY:

Employee sales data per month.

The Data sheet is a simple listing of employees’ names in column A followed by the total amount of sales in column B and the month those sales were made in column C.

Let’s now explore how to apply the QUERY function with multiple criteria to this data set.

Logical Operator AND

The logical operator AND requires that both criteria on either side of the AND operator are met in order for a row to be included in the output. For example, if you wanted to see a list of employees who sold a minimum amount AND for a specific employee, say John, the QUERY function would look something like this:

For a row to be included using this operator, both conditions in the WHERE clause must need to be satisfied.

Here’s how this would look on the working example spreadsheet:

QUERY result when using AND.

Notice with the result that John’s sales in January are not included, while the first condition was true, the second criteria was not (sales greater than 1000). Therefore, as both criteria were not met the January sales were not part of the result.

If your criteria only require one condition to be met, then you would need to apply the next logical operator.

Logical Operator OR

The logical operator OR means that if either of the two conditions is met, the row will be included in the output. For example, you may want to see a list of just the employee’s John and Jane, which would mean a formula like so:

Here is how this would look with the working spreadsheet:

Employees named John or Jane displayed.

As you can see with this result, if any condition is met the row is added to the query result. Therefore, all rows containing John or Jane are added.

For a row to be included using this operator, only one condition on either side of the condition in the WHERE clause needs to be met.

Finally, if all of your criteriums need you to exclude specific rows based on a condition, then you would need to look at applying the next logical operator.

Logical Operator NOT

The logical operator NOT excludes rows that meet the condition. For example, you could have written the previous query statement in a more succinct way by just writing:

But suppose you want to see a list of all underperforming staff and this would be those who sold in January but did NOT sell above 1,000, this can be obtained b with the formula below:

Here’s how this looks on the working spreadsheet:

Underperforming sales staff with those who did not sell more than 1,000 in Jan.

From the result of the QUERY function above, you can easily see those who did not sell above 1,000 in the month of January.

However, this does lead to one final aspect when using the logical operators in your QUERY function: ensuring consistency by using parentheses.

Combining Multiple Criteria With Parentheses

Sometimes you will need to get creative with how you combine criteria – this is where parentheses come in handy. With parentheses, you can control the order in which conditions are evaluated first.

You could have achieved a very different result if you had wrapped in parentheses the conditions after the NOT operator, like so:

This would have produced the following result:

Depending on where you place the parentheses you can get very different results.

As you can see, compared to the previous spreadsheet result in the NOT operator section, there is only one row missing: Sam’s 1,100 sales in January. And this is correct based on the parentheses that I have wrapped about both conditions.

If you are going to use parentheses in your WHERE clause the QUERY function will evaluate the innermost parentheses first before expanding out to the next set of parentheses.

In my simple example above, the innermost condition of B>1000 AND C='Jan' is met by the row of Sam’s sales, but having the operator NOT prepended outside would now mean the opposite. Therefore, every other row is fetched, and Sam’s sales are excluded.

This produces the results displayed above.

Summary

You can use multiple criteria to filter data using the Google Sheets QUERY function by joining conditions with logical operators. The three types of logical operators that can be used in the WHERE clause of your query statement are AND, OR and NOT.

To help manage complex conditions, look at wrapping your conditions with a pair of parentheses to obtain consistent results.

Click here to get a free copy of the Multiple Criteria QUERY Function Google Sheet