You can indeed shorten a Google Sheets Query formula when you want to test conditions in multiple columns in Query. It may definitely involve the logical operators AND, OR in Query WHERE clause.

I am dedicating this tutorial to all Google Sheets Query lovers. Here I’ll try to give you some idea to simplify your Google Sheets Query formula in case of conditions in multiple columns involved in it.

Actually simplifying a Query formula depend on the problem that you want to solve. So I am going to give some examples in the hope of guiding you in the correct direction.

I am going to test the following conditions in multiple columns in Query.

1. I am going to use the Logical AND operator to test whether all the specific columns (in each row) are blank.
2. In the second test, I am going to use the Logical OR operator to find whether any of the specific columns in each row have values.

In these tests, either all of the columns in question must contain string values or numbers. I mean I want to test a condition in 5 columns and all the columns must contain string values or numbers.

Let’s go to the examples to understand it in a much better way.

The OR Conditions in Multiple Columns in Query

In OR Logical test, any of the conditions, not all of the, should be met.

Let me begin with the numeric values in the column to test.

OR Test – Numeric Values as Conditions in Multiple Columns in Query

Here is the sample data. The Query formula that to simplify follows.

=query(A1:K,"Select A where C is not null or E is not null or G is not null or I is not null or K is not null",1)

How to simplify the conditions in multiple columns in this Query formula?

We can do this with the help of a virtual helper column as well as a physical helper column. Let me start with the virtual helper column first.

Using Virtual Helper Column:

First of all, see the simplified version of the Query Formula. After that, you can find the explanation.

=ArrayFormula(query({A1:K,C1:C+E1:E+G1:G+I1:I+K1:K},"Select Col1 where Col12>0",1))

To be honest, this is not the simplest version since it involves a virtual helper column. But the conditions in multiple columns in Query Where clause have been simplified.

The Query formula will be even simpler if you use the physical helper column. Before going to that let me explain what I have done in this Query to simplify it.

I have shortened this part of the Query Where clause.

where C is not null or E is not null or G is not null or I is not null or K is not null

It’s shortened to;

where Col12>0

Actually, there are 11 columns in the data in the range A1:K. You can count A to K. The twelfth column is the virtual column created in the Query data as;

{C1:C+E1:E+G1:G+I1:I+K1:K}

Since the column C, E, G, I and K contain numeric values, I have added that values and make a single column.

Using Physical Helper Column:

Just enter this helper column formula in cell L1. We have already engaged up to the column K.

=ArrayFormula(if(len(A1:A),C1:C+E1:E+G1:G+I1:I+K1:K,))

Here is the Query formula that shortened to the maximum to simplify the use of conditions in multiple columns in Query. You can put this formula in cell M1.

=query(A1:L,"Select A where L>0",1)

OR Test – Text String as Conditions in Multiple Columns in Query

Here also I am providing you both the virtual and physical helper column based solutions.

Virtual Helper Column Approach:

Here the criterion columns contain the string values so instead of adding you may combine the columns as below.

{A1:K,C1:C&E1:E&G1:G&I1:I&K1:K}

In the WHERE clause, the shortened condition will be;

where Col12 is not null

See how you can use a physical helper column to further shorten the formula.

Physical Helper Column Approach:

You can straightaway enter this formula in cell L1. It’s our helper column formula.

=ArrayFormula(if(len(A1:A),C1:C&E1:E&G1:G&I1:I&K1:K,))

Here is the shortened version of Query that to go into cell M1.

=query(A1:L,"Select A where L is not null",1)

This way you can simplify conditions in multiple columns in Query. So the OR logical part is over. Here is the next part.

The AND Conditions in Multiple Columns in Query

In AND Logical test, all the conditions should be met.

Here I am again using the same above sample data. Here is the formula to simplify.

=query(A1:K,"Select A where C is not null and E is not null and G is not null and I is not null and K is not null",1)

AND Test – Numeric Values as Conditions in Multiple Columns

I am going to simplify the AND conditions in multiple columns in this Query formula. Here is the simplified version of the formula.

Without Helper Column:

=ArrayFormula(query({A1:K,0/C1:C+0/E1:E+0/G1:G+0/I1:I+0/K1:K},"Select Col1 where Col12=0",1))

Here what does the virtual helper column do?

When you divide 0 with a number in a column, it would return 0. That means zero divided by n returns 0. But if you divide 0 with 0 in another column it would return the #DIV/0! Error because division by 0 is undefined.

If all the columns in a row have values means, the virtual column formula has the value 0 in that row.

=0/C2+0/E2+0/G2+0/I2+0/K2

See the below screenshot for more details (this is for explanation purpose only).

The 0 highlighted in cyan color indicates all the columns in that row (underlined in red color) have values, otherwise, you can see the #DIV/0! error.

In the above simplified formula, the below portion has;

where C is not null and E is not null and G is not null and I is not null and K is not null

Shortened to;

where Col12=0

With Helper Column:

Enter this formula in cell L1.

=ArrayFormula(if(len(A1:A),0/C1:C+0/E1:E+0/G1:G+0/I1:I+0/K1:K,))

Then the simplified formula in cell M1.

=query(A1:L,"Select A where L=0",1)

What about string as criteria in this case?

AND Test – Text String as Conditions in Multiple Columns

Here is the normal formula.

=query(A1:K,"Select A where C is not null and E is not null and G is not null and I is not null and K is not null",1)

Without Helper Column:

Here I suggest you to only use the helper column formula. But here also, you can use my above-simplified formula though it doesn’t look clean.

Just wrap the columns where we want to test the criteria with ISTEXT function.

=ArrayFormula(query({A1:K,0/istext(C1:C)+0/istext(E1:E)+0/istext(G1:G)+0/istext(I1:I)+0/istext(K1:K)},"Select Col1 where Col12=0",1))

With Helper Column:

Enter this formula in cell L1.

=ArrayFormula(if(len(A1:A),0/istext(C1:C)+0/istext(E1:E)+0/istext(G1:G)+0/istext(I1:I)+0/istext(K1:K),))

Use this Query in cell M1.

=query(A1:L,"Select A where L=0",1)

That’s all. Enjoy!