2

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
0

2 Answers 2

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))))

0

spreadsheet demo

11
  • Wow, this is super close! the answer should be 200. Not sure where it’s getting 250. In the source SS1, 2nd tab, I manually filtered what I’m looking for (docs.google.com/spreadsheets/d/…). In the first sheet (locked) in SS1, I included the formula I originally had written (before I realized I needed to import the range. I wonder why it’s summing the extra 50? Jul 18, 2019 at 7:10
  • because I changed N5 date hance that extra 50 >> blue cells
    – user0
    Jul 18, 2019 at 7:13
  • 1
    there is no such parameter as "Col13 is a number" nor anything similar to it
    – user0
    Jul 18, 2019 at 7:15
  • 1
    the formula from the answer is referencing spreadsheet from answer not from your sample sheet - thats why
    – user0
    Jul 18, 2019 at 7:28
  • 1
    @quesyrahsarah sheet updated
    – user0
    Jul 18, 2019 at 9:04
1

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.
2
  • thank you. actually @user0 got the formula to work! no need to separate text and number values Jul 18, 2019 at 8:33
  • @quesyrahsarah actually it separate them, just it does in a different way.
    – Rubén
    Jul 18, 2019 at 14:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.