Google Spreadsheets QUERY with multiple criteria (2 simple examples) (2023)

Google Sheets has many search and filter features. But often the most powerful is the QUERY function. Unfortunately, it can be a little tricky to use at times. And you might be wondering, "Can Google Sheets query multiple conditions?" The short answer is yes. This guide will show you, step by step, exactly how to do it. Read on for more information.

What is the Google Sheets query feature?

The Google Sheets QUERY functionallows you to apply a query to a set of data. It can help you explore areas of interest in your data and provide better understanding by allowing you to extract a subset of data from your main dataset.

You can think of a query as a filter ora pivot table. You will find that the format of the QUERY function is similar if you have ever used SQL to interact with databases. In essence, the QUERY function primarily involves executingSQL style querieson a dataset provided using Google Sheets.

Google's QUERY spreadsheet syntax with multiple criteria

Before we see the QUERY function in action, let's briefly examine how the formula works. The query syntax is:

=QUERY(data, query, header)

The parameters for the Google Sheets multi-criteria query function are:

1. data:this parameter defines the range of cells on which you want to perform a query. Each column of data can contain numeric, boolean, or string values. These can include time and dates. If there is mixed type data in a column, the data type in the majority will determine the data type of the entire column for the query. Minority data types will be considered null values.
2. consultation:this parameter defines the query you want to perform on your data. This is written inGoogle Query Visualization API Language. The query value must be enclosed in quotation marks or a cell reference containing the appropriate text.
3. header:this is an optional parameter used to define the number of header lines at the top of the data. If this parameter is omitted or set to -1, the value will be approximated based on the data parameter content.

How do logical operators work?

Logical operators are symbols or words used to create a connection between expressions, such that the value of the created compound expression depends on the value of the original expression and the meaning of the operator used.

Some commonly used logical operators include OR, AND, and NOT.

In many languages,Boolean data valuesconsists of two groups. The first group has relational operators and the second group has logical operators in their expressions. A test expression is created that regulates the flow of the program using logical operators, also called a boolean expression.

Three popular logical operators alter another Boolean operand to produce a Boolean value. These are:

1. Y:This operator requires that both terms appear in each returned value. The term is excluded from the list if one phrase appears in the document but the other does not. This operator is often used to narrow the search. For example, a search for "Google Sheets & Microsoft Excel" will return a result that contains the search terms for Sheets & Excel.
2. O:This operator requires that only one of the terms appear in the return value. For example, searching for the keywords “ecology OR pollution” will ensure that the search results include ecology, pollution, or both.
3. NO:This operator will remove all instances of the search term. For example, searching for “WITHOUT malaria” will ensure that the search results do not include the term malaria.

How to Use Multicriteria Query Google Sheets

Now we know how the QUERY function works in Sheets. Let's look at some ways to use this in your spreadsheet.

Google Sheets CONSULTATION AND

For this example, we have data for a few companies, showing their category and revenue. We would like to know the companies that madeover $60,000and they are inFoodcategory. Since we want both criteria to be true, we'll use AND as a logical operator.

Here's how to use AND for QUERY with multiple criteria in Google Sheets:

1. Click in the cell where you want to insert the formula.
2. Enter the initial part of the QUERY formula. To get started, type asame (=)signal. This will tell Google Sheets that the following text is part of the formula. now writeCONSULTATIONand add an opening bracket.
3. Now we have to enter the first parameter, which isdata. In our case it is the range of cellsA2:C7.
4. add acoma (,)to separate the parameters.
5. Now let's insert the second parameter, which isconsultation. We use this logical expression:"select A,B,C where B contains 'Food' AND C > 60000". Be sure to add the quotes or Google Sheets will return a#ERROR!
6. Add a closing bracket to the end of the formula and pressTo enter.

Google Sheets will return values ​​that meet all criteria specified by the user. As you can see in the screenshot above, it shows food companies that made over $60,000.

Google Sheets QUERY OR

Using the same data, we now want to find a business in the clothing category or located in Las Vegas. An OR query will be true if one of the conditions is true. Basically, we'll follow the same steps we used in the previous example. However, the query operator will change to fit no requirements.

The formula used to do this is:

=QUERY(A2:C7, "select A, B, C where B contains 'Clothes' OR A contains 'Las Vegas'")

Most of the formula used here is the same as used in the previous example. The only exception is the query parameter.

Explanation of how it works

Let's break it down and explain what each part of the parameter means:

• select A, B, C– This part of the parameter specifies the columns you want to select.
• where B contains 'clothes'– This is the first condition you want to check where you specify that the keyword "Clothes" should be searched for in column B.
• O– this is the logical operator used.
• A contains 'Vegas'– This is the second condition you want to check where it specifies that the keyword “Las Vegas” should be searched for in column A.

Related reading:How to Use Google Sheet Search

Common questions

How to query a condition in spreadsheets?

The QUERY function in Sheets is used to define a condition to find the data you are specifically looking for. In a way, it acts as a filter. The query will search your worksheet columns to find values ​​that match the conditions and then display the results.

The function syntax is =QUERY(data, query, header).

It requires three parameters to work. The data parameter defines the cell range on which you want to perform a query. The query parameter defines the query you want to perform on your data and is written in Google's Query View API language. Header is an optional parameter used to specify the number of header rows at the top of the dataset.

Can you filter by multiple conditions in spreadsheets?

To make your Google Sheets query contain multiple criteria, you can use the QUERY formula in Sheets and use logical operators to apply multiple conditions to a dataset. You can do this using Google's Query Visualization API language. Queries are written in a sentence-like format, and you can use the AND, OR, and NOT operators to specify the conditions you want to apply to the data. Once run, Sheets will display the results in the cell where you ran the formula.

Ending

We hope we answered your question "Can Google Sheet query for multiple criteria?" While it can take a while to learn exactly the phrases to write in the Query Display API language, learning the operators is a good start. Let us know in the comments if you have any questions. Or check out our related content for more information.

Related:

• How to use the Not equal sign in Google Sheets
• How to Connect Google Forms to Google Sheets
• Fix IFERRO in Google Sheets
• What Are Wildcards in Google Sheets?
• How to use the INDIRECT function in Google Sheets