Google Sheets – Combining IMPORTRANGE, SUMIFS, QUERY with Mixed Content

formulasgoogle sheetsgoogle-sheets-arrayformulagoogle-sheets-queryimportrange

I'm having an issue combining SUMIFS with ImportRange in Google Sheets. I found answers suggesting to nest it with QUERY. However, the issue is that one of my criteria is non-numerical.

Formula in spreadsheet 2:

 " date.bonus!a4:u"), 
 "select sum (Col13) where (Col13='<>' 
  and Col14 >= date'"&TEXT(today()-30, "yyyy-mm-dd")&"') label sum (Col13) ''")`
  • imports data from spreadsheet 1 using ImportRange
  • sums Column M values (spreadsheet 1)
  • if Column M is not blank and if Column N date is within the past month (spreadsheet 1)

I get an error message:

#VALUE! Unable to parse query string for function QUERY parameter 2: AVG_SUM_ONLY_NUMERIC

Column M values are

  • blank
  • text (yes, NA)
  • number

Column N values are

  • blank
  • text (NA)
  • dates

How can I get query condition 1 to be "Col 13 is number"?

From all the answers I found online, QUERY doesn't like non-numeric data/mixed data. Some solutions suggested using the ArrayFormula or FILTER – but these used data within the same spreadsheet (didn't ImportRange.

Other solutions used IF(ISNUMBER)) nested with my formula. I also tried changing the column format to "plain text" which didn't work.

Some formulas I've tried:

" date.bonus!a4:u")),
"select Sum (Col13) where (Col13)='<>' 
 and (Col14)>=date'"&TEXT(today()-30,"yyyy-mm-dd")&"' label sum (Col13) ''")`

Value Error: Unable to parse query string for Function QUERY parameter 2: NO_COLUMN: Col13

 " date.bonus!a4:u"), 
 "select,sum (Col13) where (Col13)='<>' 
  and Col14= date'"&TEXT(today()-30, "yyyy-mm-dd")&"' label sum (Col13) ''")`

Value Error: Unable to parse query string for Function QUERY parameter 2: PARSE_ERROR: Encountered " "," ", "" at line 1, column 7. 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" … … … … … … "(" … "*" … "-" …

Is there a way to get my formula to work without adding a new column to my source data? I'm wondering if I can use ISNUMBER or ArrayFormula?


Here are some sample sheets:
Sheet 2 (contains formula)
Sheet 1 (source data)

To summarize, my issue is QUERY not summing numbers in Col M (Col13). In QUERY I've tried using:

  • <> and <>"" (is not blank)
  • Col13 is not null
  • Col13>0

Best Answer

 "1HrEGCH-MCfeGh952VkpONRfSoLbZVLir5BNgFWK0PH8", " date.bonus!A5:O")*1),
 "select Col13 
  where Col13 is not null 
    and Col14 >="&DATEVALUE(TODAY()-30), 0))))


spreadsheet demo