Google Sheets query function needing to provide 0 value for no value found

1
votes

The query function looks at a separate sheet with orders entered by a dealer. In some months the dealer may not have a order. The B:B column in current sheet is all the dealers in Ascending order. Then I have separate columns for by month view with 3 columns per month. Number of contracts, amount then average. My query calculates correctly, but if a dealer doesn't have a contract then it skips. So my list is out of order. I'm needing it to place a 0 if no value found. I have 2 versions of the query. J = amount, H = Dealer name, A = Date, the B = is the dealer list in current sheet. This query populates but out of order due to skipping NUll or NA. =QUERY('2021ContractsData'!A:V,"Select COUNT(J),SUM(J),AVG(J) WHERE MONTH(A)+1=1 Group By H LABEL COUNT(J) 'Contracts',SUM(J) 'Amount',AVG(J) 'Average'") This query populates nothing, it shows the Header names but no values in rows. =QUERY('2021ContractsData'!A:V,"Select COUNT(J),SUM(J),AVG(J) WHERE MONTH(A)+1=1 AND H='"&B2:B&"' Group By H LABEL COUNT(J) 'Contracts',SUM(J) 'Amount',AVG(J) 'Average'")

Thank you for taking the time to read and answer. Google Sheet View

1
google-sheetsnullaveragevlookupgoogle-query-language
share a copy of your sheet – player0
Please see link below. docs.google.com/spreadsheets/d/… – geneavone

1 Answers

0
votes

try in C2:

={"Contracts", "Amount", "Average"; 
 INDEX(IFNA(VLOOKUP(B3:B, QUERY('2021ContractsData'!A:V,
 "select H,count(J),sum(J),avg(J) 
  where month(A)+1=1 
  group by H"), {2,3,4}, 0), 0))}