How to Filter with QUERY for Multiple Criteria in Google Sheets (An Easy Guide)

The QUERY function is perhaps the most customizable function to search and present specific values in Google Sheets from a source. So, in this article, we will see how we can use the QUERY function to filter for multiple criteria in Google Sheets.

But first, let’s have a look at how filtering works with the QUERY function.

Table of Contents

Understanding How QUERY Works to Filter Values in Google Sheets

Note: This section covers the basics of the QUERY function used to filter. If you want to go directly for the tutorial regarding multiple conditions, then please skip ahead.

The QUERY function essentially looks through a data range and performs a user-defined query on that range. It is highly dependent on the semantics of the query and thus it can perform simple functions like search and filter quite easily.

The QUERY function syntax:

QUERY(data, query, [headers])

For this example, let’s consider the following worksheet:

From here, we want to filter all entries that have an “In Stock” value of fewer than 1000 units.

Step 1: Open the QUERY function and input the data range.

Step 2: Begin the query by using the Select clause to input the columns you want to filter. In this case, it is all of the A, B, and C columns.

Important: The query must be within quotation marks (“”).

Step 3: Apply the filter condition after the Where clause.

Step 4: Close quotation (if needed) and parentheses and press ENTER to see the result.

=QUERY(A3:C23,"Select A, B, C Where C<1000")

This is the basic syntax to query for filtering certain values. Following this idea, we can add more conditions as required. Let’s see how it’s done.

How to Filter with QUERY for Multiple Criteria in Google Sheets

Filter for the AND Condition for Multiple Criteria

The AND condition for multiple criteria means that the criteria are dependent on each other (AND logical calculation).

For example, let’s say we want to filter entries from the West region and the values that have the In Stock value of under 1000.

Step 1: We start off as usual by inputting the data range and the columns that will be included in the filter. It is after the Where clause that we input our conditions.

Step 2: As you might have noticed, we have two different types of values for the filter conditions. One is text and the other is numerical.

To filter for “West”, we must use the Contains clause as it queries for a text value:

A Contains 'West'

Note: The text value must be within single quotes (‘’).

The condition to filter for values greater than 1000:

C<1000

Step 4: Close quotation (if needed) and parentheses and press ENTER to see the result.

=QUERY(A3:C23,"Select A, B, C Where A Contains 'West' and C<1000")

Extra: The Versatility of the Contains Clause

The Contains clause can also work with partial text matches.

For example, let’s change our query to look for the string “st” instead:

=QUERY(A3:C23,"Select A, B, C Where A Contains 'st' and C<1000")

However, it is case-sensitive.

Filter for the OR Condition for Multiple Criteria

Much like the AND condition, the OR condition for multiple criteria filtering with QUERY in Google Sheets can be done by simply inputting the word “or” in between the criteria.

Thus, without going into much detail, the formula for filtering all entries that contain the region “East” or that have more than 1000 units in stock is:

=QUERY(A3:C23,"Select A, B, C Where A Contains 'East' or C>1000")

Filter for “Does Not Contain Value” Condition for Multiple Values

The “does not contain value” condition is logically inverse to finding the conditions for filtering. In the other methods to filter, it has always been to set up the condition as FALSE to get the desired values. And we’ll do the same here.

For inverting a logical condition in QUERY, we simply have to use the keyword “Not” before the query condition.

Let’s see the example where we filter for all values except the region “East” that are not under 1000 units in stock.

The formula is:

=QUERY(A3:C23,"Select A, B, C Where Not A Contains 'East' And Not C<1000")

Taking Advantage of the Matches Clause in QUERY to Filter for Multiple Criteria in Google Sheets

When it comes to looking for specific text conditions, the use of wildcards makes things quite simple. Thankfully, we can also utilize this lookup approach of strings in QUERY with the help of the Matches clause.

The Matches clause directly replaces the Contains clause or the QUERY function. And with Match, we can use regular expressions, albeit the lookup syntax is a bit different.

Where in the Contains clause we simply input the string inside single quotes, the Match clause additionally requires the condition string to be further enclosed by the period-asterisk (.*) wildcards.

We see this in the following image example where we filter for the region East and the product Bricks:

=QUERY(A3:C23,"Select A, B, C Where A Matches '.*East.*' and B Matches '.*Bricks.*'")

While at first glance, it may look like extra steps, using wildcards does have some advantages when used with logical expressions.

For example, we can set the OR condition for the multiple criteria filter with QUERY in Google Sheets by using the vertical bar (|) symbol in between the conditions:

=QUERY(A3:C23,"Select A, B, C Where A Matches '.*East.*|.*Bricks.*'")

Final Words

That concludes all the different iterations to filter for multiple conditions using the QUERY function in Google Sheets. As you have seen, the query function can easily take the requirements of the user to give exactly what they are looking for. And filtering values with QUERY is that simple to do.

Feel free to leave any queries or advice you might have in the comments section below.