How to Use Query Function in Google Sheets with Multiple Criteria

In Google Sheets, data can be filtered based on various criteria using the QUERY function. The WHERE keyword in the QUERY function allows data filtering using multiple criteria. There are three logical operators, namely AND, OR, and NOT, which can be used to combine these criteria.

For example, if there is a sheet containing employee names in one column A, the total sales in another column B, and the corresponding month of those sales in a third column C, the QUERY function can be applied to extract specific data from this sheet.
 

To accomplish this, rules like “Filtering employees with sales exceeding $100 in January” or “Retrieving a list of employees with sales in either February OR March” can be established. The QUERY function enables the retrieval of this information by applying these rules.

Therefore, Google Sheets’ QUERY function permits data filtering in various ways by employing multiple criteria.

1) Logical Operator AND

The Logical Operator AND needs two conditions to be true for a row to show in the result. For instance, if somebody wants a list of workers who both sold a certain amount and are a particular worker, like John, the QUERY function would appear as follows:

=QUERY(A:C, "SELECT * WHERE A='Charlie' AND B<1000")

To include a row using this operator, both the conditions in the “WHERE” clause must be fulfilled.

Take a look at the result, you won’t find Charlie’s April sales. Although the first condition was valid and fulfilled the second condition (sales do not exceed 1000) wasn’t fulfilled. Consequently, since both conditions weren’t satisfied, April sales weren’t included in the result.

Note: If the criteria only require one condition to be met, then logical operator OR should be considered.

2) Logical Operator OR

The OR operator requires any one of two conditions to be true. For example, if somebody wants a list that includes employees with the name Charlie or Nishi, then the formula given below will be used.

=QUERY(A:C, "SELECT * WHERE A='Charlie' OR A='Nishi'")

Using this QUERY will give the result as shown below:

To include a row using this operator only one condition needs to be fulfilled on either side of the condition in the WHERE clause.

Note: If all criteria require excluding certain rows based on a condition, then the NOT logical operator should be used.

3) Logical Operator NOT

The logical operator NOT excludes rows that meet a specific condition. For example, in the given sales data list that we used above, one can use the NOT operator to find staff who didn’t sell over 1,000 in May. This will help in identifying underperforming staff.

The NOT operator can be used in a QUERY as:

=QUERY(A:C, "SELECT * WHERE NOT B > 1000 AND C='May'")

This QUERY will give a list of employee names who did not make sales more than 1000 in the month of May. The result of this QUERY is shown below.
 

Organizing responses from Google Forms can be challenging, especially when dealing with large datasets. Leveraging the power of query functions in Google Sheets can streamline this process, allowing you to filter, sort, and analyze data efficiently.

Here, we’ll see how to organize Google Form responses with query functions, ensuring you can manage and interpret your data with ease. Also, we’ll see how to effortlessly organize your information, making it more accessible and valuable in google form.