Filtering data in Google Sheets just got easier. With the Google Sheets QUERY function, you can filter your sheets using multiple criteria. This means you can apply two or more conditions to filter your data using Google Sheets QUERY.
In this article, we will explain how to use the Google Sheets QUERY function with multiple criteria in an easy way. We will include simple examples of using Google Sheets QUERY that anyone, regardless of experience, can understand.
Whether you are a complete beginner or have some experience with Google Sheets, using multiple criteria with Google Sheets QUERY can take your data analysis to the next level.
After reading today’s guide, you’ll be able to filter your data like a pro using multiple conditions with Google Sheets QUERY.
Let’s get started by understanding the basics.
Understanding Google Sheets QUERY Function
The Google Sheets QUERY function is like a superpowered search tool for your data. It lets you explore and filter your information in really useful ways.
Think of the QUERY function as a filter or lens that helps you zoom in on the parts of your data that you want to look at more closely. For example, if you had a giant spreadsheet with all your company’s sales numbers for the year, you could use QUERY to instantly see only the data for your top ten salespeople or only data from March.
The QUERY function works using a format that is similar to SQL code. But don’t let that scare you. SQL is the programming language used to manage databases. So, in plain English, all this means is that the QUERY function allows you to query or ask questions about your spreadsheet data.
Simply put, QUERY is an easy way to discover trends, filter out unnecessary information, and understand what is happening in your Google Sheets data. It makes working with big spreadsheets much simpler.
Google Sheet QUERY With Multiple Criteria: Understanding the Syntax
The QUERY formula might look complicated at first, but it’s actually pretty simple once you break it down. Here is the basic structure:
=QUERY(data, query, header)
Now, let’s break down what each part of this formula means in simple terms:
- Data: This is the part where you tell the QUERY function which cells you want to look at in your spreadsheet. You can select a range of cells containing different types of information like numbers, text, dates, or even times. One important thing to remember is that if a column has different data types, QUERY will mostly consider the type that appears the most. Any other types will be treated as if they don’t exist in that column.
- Query: Here, you specify what you want QUERY to do with your data. This is where you ask your question or set your criteria. The way you write this query is a bit special—it uses Google’s own language called the Query Visualization API Language. Don’t worry; it’s not as complicated as it sounds. Just remember to put your query text inside quotation marks, or you can reference another cell that contains your query text.
- Header: This last part is optional. It’s used to tell QUERY if your data has rows at the top that are headers (titles or labels for your columns) and how many header rows there are. If you don’t specify this or use -1, QUERY will try to figure it out by itself based on your data.
By understanding these three parts, you can use the QUERY function to manage and analyze your data in Google Sheets even as a beginner.
Understanding How Logical Operators Work?
When querying your data, you can combine multiple filters using simple words like AND, OR, and NOT. Think of these as ways to fine-tune your search.
- AND – Returns only results that match both filters.
For example, show sales where the Region is West AND Revenue exceeds $1000.
This will filter to only West region sales with revenue over $1000. Both conditions must be met.
- OR – Returns results that match either filter.
For example, show customers where City is Boston OR City is Denver
This will display customers from both Boston and Denver. It matches if one OR both conditions are true.
- NOT – Omits results that match the filter.
For example, show products where the Category is NOT Furniture.
This will display all products except those in the Furniture category. Anything matching Furniture is excluded.
You can even combine them like:
Revenue greater than $1000 AND (City is Boston OR Region is West)
See how the brackets group the OR condition? The key is understanding that AND, OR, NOT alter the filters to include or exclude specific data. They give you more fine-tuned control over your queries.
Copy Sample Sheet
Want to follow along with today’s tutorial? Click the link below to access our sample sheet.
Click Here to Copy The Sample Sheets
Using the QUERY Function in Google Sheets for Multiple Criteria: Practical Examples
Now that you understand the basics of the QUERY function in Google Sheets. Let’s explore how you can put this tool to use. In this section, we will look at a couple of examples. Let’s get started, shall we?
Example 1: Google Sheets QUERY AND
Let’s explore how to filter data in Google Sheets using “AND” in the QUERY formula, allowing us to apply two criteria.
Imagine we have a spreadsheet of student test score data. We want to analyze how students performed on different test subjects.
Our data set includes these columns:
- Student Name: The student’s first name
- Subject: The name of the test subject – Math, Science, History, English
- Score: The student’s numeric test score from 0 to 100
Here’s a snapshot of what our sample data looks like (this would normally be a larger dataset with rows for all students):
Now, let’s say we only want to view math scores over 85. This involves two criteria:
- The subject must equal “Math.”
- The score must be greater than 85
Since we need results meeting BOTH filters, we use the AND logical operator in our QUERY.
Here is how to go about it:
Step 1: Choose A Blank Cell
Let’s begin by choosing a blank cell in our sample sheet. This is where we want the QUERY result to be generated. For this example, we will go with cell F4.
Step 2: Type in The QUERY Formula
With the cell where you want the query result generated selected, navigate to the formula bar and type in the following formula:
=QUERY(A3:C9,”Select A, B, C WHERE B = ‘Math’ AND C > 85″)
Breaking this down:
- A3:C9 = This is our dataset range
- Select A, B, C = Shows the Name, Subject, and Score columns in our results
- Where B = ‘Math’ = Filter Subject to only Math
- AND C > 85 = Also filter Score to be over 85
Step 3: Press Enter
With the QUERY formula typed in exactly as we showed you in the previous step. All that is remaining is to press the Enter button on your keyboard. This action authorizes Google Sheets to generate the QUERY result in the selected cell.
If you did exactly as we showed you, you should get something like this:
From the screenshots above, you can see that we have been able to use the AND QUERY to filter out the names of other students who didn’t meet the criteria.
Example 1: Google Sheets QUERY OR
Previously, we filtered our student score data to only show Math scores over 85 using AND. This returned students meeting both criteria.
Let’s say now we want to broaden our filter to include Science scores over 85 as well. This means a student qualifies if they have EITHER:
- Math score over 85 OR
- Science score over 85
Since we only need one of the criteria to be true, we use OR in our query.
Let’s get started.
Step 1: Choose An Empty Cell
Like in the first example, let’s choose an empty cell in our spreadsheet. Ideally, this blank cell will house the QUERY result. For this example, we will go with cell F4.
Step 2: Type Query Formula
Having selected the empty cell where you want the QUERY result generated. Let’s go ahead and enter the QUERY formula. Basically, what you want to do is navigate to the formula bar and type in the following formula:
=QUERY(A1:C9, “Select A, B, C where (B = ‘Math’ AND C > 85) OR (B = ‘Science’ AND C > 85)”)
Breaking this formula down:
- A1:C9 = Our data range
- Select A, B, C = Return name, subject, score
- Where = Apply filter criteria
- (B = ‘Math’ AND C > 85) = Math scores over 85
- OR = Either this criteria or the next
- (B = ‘Science’ AND C > 85) = Science scores over 85
Step 3: Press Enter
Now that you have entered the formula as we showed you, it’s time to get the QUERY result. All we need to do to generate the result is press the Enter button on our keyboard. This action authorizes Google Sheets to create the result in the selected cell.
Here is what ours looks like:
From the screenshot above, you can see that Jane and Mark qualify because they have Math scores over 85.
Additionally, John is now included because with the OR, his high Science score meets the second criteria.
Now, you see how to seamlessly use the OR Query to filter data in a spreadsheet. That wasn’t too challenging, was it?
Frequently Asked Questions
How Do I Filter Data Using Multiple Conditions In Google Sheets?
You can filter your spreadsheet data based on multiple criteria using the QUERY function and logical operators like AND, OR, and NOT. For example,
=QUERY(A1:C10,”where A = ‘Sales’ AND C > 1000″)
Can I Use Or And And Together When Filtering Data In Google Sheets With Query?
Absolutely. QUERY allows you to combine AND and OR to create more complex filters. For instance,
=QUERY(A1:E,”where C = ‘Complete’ AND (D = ‘John’ OR E = ‘Marketing’)”)
What Other Logical Operators Can I Use With Google Sheets Query Formulas Besides And, Or And Not?
A few others that are handy include:
- LIKE – acts as a wildcard for partial text matches
- <> or != – finds values not equal to criteria
- , <, >=, <= – for numeric comparisons like greater than or less than
I’m Getting Errors – What’s Wrong With My Google Sheets Query Formula?
A couple of things to check:
- The data range includes headers
- Data types mismatch between rows
- Bracket use for order of operations
- Single quotes around text values
Running some test queries on a sample sheet can help debug. If you get an error message when applying the QUERY formula, check the syntax carefully for typos.
How Do I Sort The Results Of My Google Sheets Query Formula?
You can add an “ORDER BY” clause to your QUERY formula to sort the returned data. For example:
=QUERY(A1:C,”Select A, B, C WHERE B = ‘Math’ ORDER BY C DESC”)
This will sort our score data with the highest math scores at the top.
The key points are:
- ORDER BY goes after the initial WHERE filters
- Pick the column to sort by (C in our example)
- ASC sorts low-to-high, DESC sorts high-to-low
So, ORDER BY gives you control over how the filtered rows are sorted in the query output. Feel free to add it to any QUERY formula when you want sorted results.
Final Thoughts
If you have read to this point, you should now fully understand the basics of using the powerful QUERY function in Google Sheets to filter your data with multiple criteria.
In this guide, we covered:
- What the QUERY function is, and how it lets you explore spreadsheet data
- The simple syntax for writing QUERY formulas
- Using logical operators like AND, OR, NOT to combine filters
- Step-by-step examples applying multiple criteria queries
- Answers to frequently asked questions
Learning to use QUERY to its full potential does take some practice. But by mastering a few key concepts like the ones we discussed here, you’ll find it makes working with large datasets much more manageable.
The key is to start simple and slowly build more complex queries. Test things out on a sample sheet first whenever trying something new. And don’t worry about memorizing every command – Google is there to help, too.
We hope this article gave you a good overview of using Google Sheets QUERY with multiple criteria.