Google Sheets has plenty of lookup and filter functions. But, often the most powerful one is the QUERY function. Unfortunately, it can sometimes be a little tricky to use. And you may have been 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 to learn more.
Table of Contents
What Is the Google Sheets Query Function?
The Google Sheets QUERY function enables you to apply a query on a dataset. It can help you explore areas of interest in your data and provide you with a better understanding by allowing you to extract a subset of data from your main dataset.
You can think of a query like a filter or a pivot table. You’ll discover that the QUERY function’s format is similar if you’ve previously used SQL to interact with databases. In essence, the QUERY function mostly entails performing SQL-style queries on a supplied dataset using Google Sheets.
The Syntax for Google Sheets QUERY With Multiple Criteria
Before looking at the QUERY function in action, let’s briefly examine how the formula works. The syntax for QUERY is:
=QUERY(data, query, header)
The parameters for the query function Google Sheets multiple criteria are:
- data: this parameter defines the cell range you wish to perform a query on. Every column of data can hold numeric, boolean, or string values. These can include time and dates. If there is data of mixed types in a column, then the data type in the majority will determine the data type of the entire column for the query. The data types in the minority will be considered null values.
- query: this parameter defines the query you wish to perform on your data. This is written in the Query Visualization API Language by Google. The query’s value should be inside quotation marks or a cell reference containing the appropriate text.
- header: this is an optional parameter used to define the number of header rows toward the top of the data. If this parameter is omitted or set as -1, then the value will be approximated based on the data parameter’s content.
How Do Logical Operators Work?
Logical operators are symbols or words used to create a connection between expressions so that the value of the created compound expression depends on the value of the original expression and the meaning of the used operator.
Some commonly used logical operators include OR, AND, and NOT.
In many languages, Boolean data values consist of two groups. The first group has relational operators, and the second group has logical operators in their expressions. A test expression that regulates program flow is created using logical operators also called a Boolean expression.
Three popular logical operators change another Boolean operand to produce a Boolean value. These are:
- AND: This operator requires both terms to appear in every value to be returned. The term is excluded from the list if one phrase appears in the document, but the other does not. This operator is usually used to narrow down the search. For example, a search for “Google Sheets AND Microsoft Excel” will show a result that contains both the Sheets and Excel search terms.
- OR: This operator requires only one of the terms to appear in the value to be returned. For example, searching the keywords “ecology OR pollution” will ensure that the search results include either ecology, pollution, or both of the search terms.
- NOT: This operator will remove all instances of the term from the search. For example, searching “NOT malaria” will ensure that the search results do not include the term malaria.
How to Use Query Multiple Criteria Google Sheets
We now know how the QUERY function works in Sheets. Let’s take a look at some ways you can use this in your sheet.
Google Sheets QUERY AND
For this example, we have the data of some businesses, which shows their category and revenue. We would like to know the companies that made more than $60,000 and are in the Food category. As we want both criteria to be true, we will use AND as the logical operator.
Here is how to use AND for QUERY with multiple criteria in Google Sheets:
- Click on the cell where you wish to enter the formula.
- Type in the initial part of the QUERY formula. To start, enter an Equal (=) sign. This will tell Google Sheets that the following text is a part of the formula. Now write QUERY and add an opening bracket.
- Now we have to enter the first parameter, which is data. In our case, it is the cell range A2:C7.
- Add a Comma (,) to separate the parameters.
- Now we are going to enter the second parameter, which is query. We use this logical expression: “select A, B, C where B contains ‘Food’ AND C>60000”. Make sure to add the quotation marks, or Google Sheets will return a #ERROR!
- Add a closing bracket at the end of the formula and press Enter.
Google Sheets will return the values that fulfill all criteria specified by the user. As you can see in the above screenshot, it shows the food businesses that made over $60,000.
Google Sheets QUERY OR
Using the same data, we now wish to find either a business in the clothing category or one located in Las Vegas. An OR query will be satisfied if either one of the conditions is met. We will essentially follow the same steps we used in the previous example. However, the query operator will be changed to fit without requirements.
The formula used to do this is:
=QUERY(A2:C7, “select A, B, C where B contains ‘Clothing’ OR A contains ‘Las Vegas'”)
Most of the formula used here is the same as the one 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 wish to select.
- where B contains ‘Clothing’ – this is the first condition you wish to check where it is specified that the keyword “Clothing” should be looked for in the B column.
- OR – this is the logical operator used.
- A contains ‘Las Vegas’ – this is the second condition you wish to check where it is specified that the keyword “Las Vegas” should be looked for in the A column.
Related Reading: How to Use Lookup in Google Sheets
Frequently Asked Questions
How Do I QUERY a Condition in Sheets?
The QUERY function in Sheets is used to set a condition to find the data you are specifically looking for. It somewhat acts as a filter. The query will search the columns in your spreadsheet to find values that match the conditions, then display the results.
The syntax for the function is =QUERY(data, query, header).
It requires three parameters to work. The data parameter defines the cell range you wish to perform a query on. The query parameter defines the query you want to perform on your data and is written in the Query Visualization API Language by Google. The 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 Sheets?
For Google Sheets query contains multiple criteria, you can use the QUERY formula in Sheets and use logical operators to apply numerous conditions in a dataset. You can do this by using the Query Visualization API Language by Google. The queries are written in a sentence-like format, and you can use the AND, OR, and NOT operators to specify the conditions you wish to apply to the data. Once executed, Sheets will display the results in the cell where it executed the formula.
Wrapping Up
We hope we’ve answered you question of “Can Google Sheet query multiple criteria?” While it may take you a while to learn exactly the phrases you should write in Query Visualization 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 to learn more.
Related:
