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:
=query(ImportRange("https://docs.google.com/spreadsheets/d/1p_F-1tyYhlHfjjE2151T52FvcwGrtKUvB12WSJR0WRT",
"inquiries.app 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
FYI
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:
=query(isnumber(ImportRange("https://docs.google.com/spreadsheets/d/1p_F-1tyYhlHfjjE2151T52FvcwGrtKUvB12WSJR0WRT",
"inquiries.app 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
=query(ImportRange("https://docs.google.com/spreadsheets/d/1p_F-1tyYhlHfjjE2151T52FvcwGrtKUvB12WSJR0WRT",
"inquiries.app 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
?
EDIT
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