Combining IMPORTRANGE, SUMIFS, QUERY with cells that contain numbers and text

Question

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

Answer 1

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

spreadsheet demo

Answer 2

The problem is that Column M and Column Nfrom the source spreadsheet include text and numbers / dates. Blank cells doesn't cause problems.

In both columns, you should remove the text values before passing the imported values to QUERY.

While it could apparently be "elegant" to have a single formula that does all the job, this makes the spreadsheet harder to understand, troubleshoot and maintain, so at least in the meantime that you understand how each task should be done, avoid to use the "all-in-one-formula" approach.

• Use one sheet for a IMPORTRANGE formula (=IMPORTRANGE(...)). The next step assumes that this formula was added to a sheet named Sheet1.
• Since your formula only use Col13, and Col14 you could use something like the following as the first argument of QUERY
  • =ARRAYFORMULA({IF(ISNUMBER(Sheet1!M:M),Sheet1!M:M,),IF(ISNUMBER(Sheet1!N:N),Sheet1!N:N,)}). Let say that this formula is added to a sheet named Sheet2.
• Use Sheet2!A:B as the first argument of QUERY but instead of Col13, and Col14 use A and B respectively.