Defining Explicit Precedence in Google Sheets Query (Logical Operators)

Published on

We must use parentheses to define explicit precedence when using logical operators AND, OR, and NOT in Google Sheets Query.

When using more than one logical operator in Query to join multiple conditions, we must use parentheses to define explicit precedence.

Otherwise, the formula won’t return the correct information that we want.

Here my sample data contains eight records that spread across seven columns. I want to retrieve the item codes (column A) based on the below conditions.

Sample Records in Sheets

Filter records if:

1. Either column B = “Yes” or column C = “Opened” (Condition 1 [highlighted in Cyan color on the image below])

And;

2. F = TRUE (Condition 2 [highlighted in Green])

3. G = TRUE (Condition 3 [highlighted in Yellow])

Here is the correct formula that will return the required output.

=QUERY(A1:G, "select A where (B='Yes' or C='Opened') and F=TRUE and G=TRUE")
Formula Example - Defining Explicit Precedence in Query

In the above example, all three conditions are met in rows 2, 3, and 9.

The parentheses around the OR operators are important.

If we want, we can use them around the AND also as show in the image above.

Here is an alternative formula that will return the same records:

=QUERY(A1:G, "select A where F=TRUE and G=TRUE and (B='Yes' or C='Opened')")

What Happens When I Do Not Define Explicit Precedence in Google Sheets Query?

Assume we have removed the parenthesis around the OR operators as below.

=QUERY(A1:G, "select A where B='Yes' or C='Opened' and F=TRUE and G=TRUE")

It will produce a different result because the query is evaluated in the following order.

Filter records if:

1. Column B = “Yes”

Or;

2. Column C = “Opened,” and Column F = TRUE, and Column G = TRUE.

Query Formula without Parentheses around Logical Operators

In the first example above, you can find an “alternative formula”. In that formula also, I have used parenthesis to define explicit precedence.

What happens when we remove them?

Formula:

=QUERY(A1:G, "select A where F=TRUE and G=TRUE and B='Yes' or C='Opened'")

This query will evaluate in the following order:

Filter records if:

1. Column F = TRUE, and Column G = TRUE, and Column B = “Yes”

Or;

2. Column C = “Opened”.

So the result will be the item codes in rows 2, 3, 4, 7, 8, and 9.

That’s all. Thanks for the stay. Enjoy!

Query Resources

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Expert Prashanth KV is a Diamond Product Expert in Google Sheets, officially recognized by Google for his contributions to the Docs Editors Help Community and featured in the Google Product Experts Directory. Explore his blog to learn advanced formulas, automation tips, and problem-solving techniques to elevate your spreadsheet skills.

Calculate Trip Days by Month (Start, End, and Full Days) in Google Sheets

If you're managing business travel in Google Sheets, you may need to calculate how...

Mode of Comma-Separated Numbers in Excel (Dynamic Array)

There is no dedicated function in Excel to directly find the mode of comma-separated...

How to Find Mode of Comma-Separated Numbers in Google Sheets

Working with comma-separated numbers inside a single cell is a common scenario in Google...

How to Count Merged Cells in Google Sheets (and Get Their Size)

Sometimes, you may have blocks of merged cells in a column or row. But...

More like this

Calculate Trip Days by Month (Start, End, and Full Days) in Google Sheets

If you're managing business travel in Google Sheets, you may need to calculate how...

How to Find Mode of Comma-Separated Numbers in Google Sheets

Working with comma-separated numbers inside a single cell is a common scenario in Google...

How to Count Merged Cells in Google Sheets (and Get Their Size)

Sometimes, you may have blocks of merged cells in a column or row. But...

6 COMMENTS

  1. Hi Prashanth! Thank you for all those great articles (: I’m so happy each time I google something I’m stuck with and find your website with the exact answer.

    I’ve tried to replicate the ‘explicit precedence’ using date criteria by excluding a date range but couldn’t make the formula returns the correct results.

    Any idea?

    Thanks!

    • Hi, Dimitri Leroux,

      I’m glad to hear that you find my tutorials worthy.

      Regarding the issue with your formula, please include the URL of that sheet in your reply below. I won’t publish that comment.

      A sample sheet URL will also do.

      • Here is the public link:

        — removed by admin —

        I changed the data to make it looks simple 🙂 Thank you so much for looking at it!

        • Hi, Dimitri Leroux,

          I’ve inserted the formula in your sheet.

          You were trying to sum the last 30 days’ data that excluding a certain date range.

          To exclude the date range, I have used the NOT operator.

          • Hi Prashanth, just realized your help with the sheet! Thanks so much, nice way around to make it matches the result again.

            I also noticed your changes with:

            – to_date(index(split( … instead of … to_date(datevalue(left(

            – arrayformula(query{ … instead of … query({arrayformula(

            Is it because of coding conventions, or it also affects the speed at which the spreadsheet is loaded? (same as putting formula inline)

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.