Query with OR statement in Sheets

Question

I'm trying to get a query working and so far this works:

=QUERY({Sheet1!A2:G; 'Sheet2'!A2:G; 'Sheet3'!A2:G;'Sheet4'!A2:G}, "where Col1 is not null order by Col1 asc")

When I add the second condition with an OR I can't get it to work. So far I've tried:

=QUERY({Sheet1!A2:G; 'Sheet2'!A2:G; 'Sheet3'!A2:G;'Sheet4'!A2:G}, "where Col1 is not null OR where Col2 is not 'complete' order by Col1 asc")

or

 =QUERY({Sheet1!A2:G; 'Sheet2'!A2:G; 'Sheet3'!A2:G;'Sheet4'!A2:G}, "where Col1 is not null OR (Col2 != 'complete') order by Col1 asc")

What am I doing wrong?

Answer 1

The where keyword should be used only once.

The not equal operator is <> not !=.

Use parenthesis between conditions

• instead of Col1 is not null OR (Col2 != 'complete')

• try (Col1 is not null) OR (Col2 != 'complete')

Reference

Query Language Reference

Answer 2

I ended up completely rewriting the query and using the IMPORTRANGE function which allowed the data filter option from the menu to remove the completed items which was the objective. I couldn't find a solution to a workable 2nd condition with the OR. The below example works. There is probably a more ideal solution but I couldn't get it to work.

• Use semicolons between data to stack vertically (verses commas which will stack them side by side).
• Be sure to use where clause "Col1 is NOT NULL" to filter out the empty rows, otherwise, your data will be listed further down on the sheet after the empty rows.
• The sort function replaces the order by clause.

Here is the final code broken apart for readability

=SORT(
   QUERY({
      IMPORTRANGE("GoogleSheetHash","Sheet1!A2:G");
      IMPORTRANGE("GoogleSheetHash","Sheet2!A2:G");
      IMPORTRANGE("GoogleSheetHash","Sheet3!A2:G");
      IMPORTRANGE("GoogleSheetHash","Sheet4!A2:G")},
      "select * where Col1 is NOT NULL",0
      )
,1,TRUE
)

Answer 3

I found this formula to work:

=QUERY(Raw!A2:E, "SELECT A,B,C,D,E WHERE D='Jenn' OR E='Jenn' ",-1)

Replace the following:

Raw!A2:E = Your range
Jenn = Your search criteria