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

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:

``=QUERY(Data!A:C, "SELECT * WHERE A='John' AND B>1000")``

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:

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:

``=QUERY(Data!A:C, "SELECT * WHERE A = 'John' OR A = 'Jane'")``

Here is how this would look with the working spreadsheet:

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:

``=QUERY(Data!A:C, "SELECT * WHERE NOT A='Sam'")``

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:

``=QUERY(Data!A:C, "SELECT * WHERE NOT B>1000 AND C='Jan'")``

Here’s how this looks on the working spreadsheet:

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:

``=QUERY(Data!A:C, "SELECT * WHERE NOT (B > 1000 AND C='Jan')")``

This would have produced the following result:

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.

Ryan Sheehy
Ryan has been dabbling in code since the late '90s when he cut his teeth exploring VBA in Excel. Having his eyes opened with the potential of automating repetitive tasks, he expanded to Python and then moved over to scripting languages such as HTML, CSS, Javascript and PHP.