1

I have a Google Sheets query below that returns an error due to the usage of the reserved word "BY."

I have tried back quotes and backquotes with parentheses to escape the "BY" but I am unsure of the syntax to escape both the "$B4:BY" and the "COMUMN(BY3)."

=sumproduct(query($B4:BY,"select " & SUBSTITUTE(ADDRESS(1,COLUMN(BY3),4), "1", "") & " where B<>'Hide'"))

I am positive my formula works because it works in adjacent columns BX and BZ. However in column BY it returns the following #VALUE error:

Unable to parse query string for Function QUERY parameter 2: PARSE_ERROR: Encountered " "by" "BY "" at line 1, column 8. Was expecting one of: "true" ... "false" ... "date" ... "timeofday" ... "datetime" ... "timestamp" ... "min" ... "max" ... "avg" ... "count" ... "sum" ... "no_values" ... "no_format" ... "is" ... "null" ... "year" ... "month" ... "day" ... "hour" ... "minute" ... "second" ... "millisecond" ... "with" ... "contains" ... "starts" ... "ends" ... "matches" ... "like" ... "now" ... "dateDiff" ... "quarter" ... "lower" ... "upper" ... "dayOfWeek" ... "toDate" ... ... ... ... ... ... "(" ... "*" ... "-" ...

player0
  • 124,011
  • 12
  • 67
  • 124
Etchisketchistan
  • 11
  • 2

1 Answers1

1

try:

=SUMPRODUCT(QUERY(B4:BY, "select `BY` where not B = 'Hide'", 0))

BY column should be escaped by single backquotes:

``

Community
  • 1
  • 1
player0
  • 124,011
  • 12
  • 67
  • 124