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.

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