0

doubleunary kindly helped with the linked question however i'm struggling to amend the Query() section so as to pull different results. ie. for repairs and revenue also.

Google Sheets - Formula or Code?

I amended as per below but i'm getting the #Value error so quite obviously did this wrong

=query( R13:W, "select lower(W), sum(r) where W is not null group by lower(W) label lower(W) '', sum(r) 'Repairs' ", 0 )

The linked image below should help with explaining what i mean, any questions please ask.

Image of sample spreadsheet

1
  • Please do not paraphrase errors but quote verbatim the exact error message you received. Jan 15, 2023 at 17:46

1 Answer 1

0

Column labels are case-sensitive in a query statement so sum(r) will not work. Further, in your sample spreadsheet, the number of repairs is not in column R but in column Q. Your use of merged cells is making that hard to see. Use this formula:

=query( 
  Q13:W20, 
  "select lower(W), sum(Q) 
   where W is not null 
   group by lower(W) 
   label lower(W) '', sum(Q) 'Repairs' ", 
  0 
)
6
  • I'm getting the below error: Error Unable to parse query string for Function QUERY parameter 2: AVG_SUM_ONLY_NUMERIC Jan 15, 2023 at 18:02
  • I'm trying to use the W column for the Weeks and moved the query formula to column AA, is this wrong or does the original part need to change also? Jan 15, 2023 at 18:04
  • The error message indicates that column R does not contain numbers. Perhaps it contains text strings that look like numbers, or mostly nulls and very sparse numeric data. You can check the data with the isnumber() function. Consider sharing a publicly editable sample spreadsheet. There is a blank sheet maker that lets you share safely. Jan 15, 2023 at 18:21
  • I believe this should be a link to a copy of the sheet: docs.google.com/spreadsheets/d/… Jan 15, 2023 at 19:11
  • Edited the answer per your new requirements. Jan 15, 2023 at 20:00

Your Answer

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

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