'Query formula not working with error AVG_SUM_ONLY_NUMERIC

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)

Not working query The data

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

arraysgoogle-sheetsgoogle-sheets-formulaarray-formulasgoogle-query-language


Solution 1:[1]

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

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 player0

Related Questions