0
votes

I am trying to get the average value across multiple tabs in a google sheet, using different sets of criteria.

One of this is if the Date in column 1 is equal to the one in Cell B62 of tab "Sheet1"

The query works on the first two criteria but not on the date one.

=QUERY({'Raw Data - Google Ads'!A:L,'Raw Data - Bing Ads'!A:Q},"select Avg(Col11) where Col4 contains 'EMEA' and Col4 contains 'BKWS' and Col1 contains '&Sheet1!B62&'",1)

No error message is displayed, the query result is just blank.

1

1 Answers

0
votes

Google query needs the following specific syntax for comparing dates:

where startDate < date "2008-03-18"

Ref docs here.

The fix may be:

=QUERY({'Raw Data - Google Ads'!A:L,'Raw Data - Bing Ads'!A:Q},"select Avg(Col11) where Col4 contains 'EMEA' and Col4 contains 'BKWS' and Col1 contains date '"&TEXT(Sheet1!B62,"yyyy-mm-dd")&"'")

Essentially, use date with the date in the yyyy-mm-dd format.