Query formula not working with error AVG_SUM_ONLY_NUMERIC

Question

I am getting this error on my query.

Unable to parse query string for Function QUERY parameter 2: AVG_SUM_ONLY_NUMERIC

I don't understand whats the problem is here. actually, I have 2 columns in my data sheet with "W" is Gross Sale "Z" is the Net sale My query is perfectly working with column "W" which is a Currency cell, and numbers are manually entered for each row while not working with column "Z" which is a calculated cell with the below formula

IF(ISBLANK(W2)," ",SUM(W2-X2)-Y2) I am also unable to make this formula as an array formula (might need help here too)

PS: I can share the sheet if anyone want to help directly on the sheet.

Hello, I have somewhat achieved what i wanted by apply array formula to column Z, now my query doing what it should do. but issue in now in column Z. column Z showing $0.00 where Calculated Column are empty. docs.google.com/spreadsheets/d/… here is the link. waiting for your input — Ali Ahmed
Since what you are describing looks to be another issue, please post a new question in this case. @AliAhmed — ale13

player0 player0 · Accepted Answer · 2021-01-30T08:05:48

try:

=ARRAYFORMULA(QUERY({Sheet1!D1:D5000, Sheet1!W1:W5000, 
 IFERROR(REGEXEXTRACT(Sheet1!Z1:Z5000, "\d+.\d+|\d+"))*1}, 
 "select Col1,sum(Col3),count(Col3)
  where Col2 is not null
  group by Col1 
  order by Col1 desc
  label sum(Col3)'Gross Sale', count(Col3)'Total Orders'"))

Query formula not working with error AVG_SUM_ONLY_NUMERIC

1 Answers