I have a large table of 20 columns that contain 3 important columns of sales data by sales staff: Name, Qty, and Average sale price (designated Col1, Col2, Col3, respectively). To calculate average sale price of item using standard formula, I use =Sumproduct(C4:C8, D4:D8)/Sum(C4:C8), which gives a price of $13.52.

I tried to write a matching Query clause in D16 to calculate average sale price of item using: Query(B4:D8, "Select Sum(Col2*Col3)/Sum(Col2)").

However, this generates a "parsing" error" (see D18). Clearly, Summing product of 2 columns together then dividing by sum of one column requires a certain syntax. Are helper columns required? Any help with this one? Please note this has to be in a Query statement (Not SUMIF or Filter statements please). Here is the link to the sheet.

Thank you all for your help. Badr

I have a large table of 20 columns that contain 3 important columns of sales data by sales staff: Name, Qty, and Average sale price (designated Col1, Col2, Col3, respectively). To calculate average sale price of item using standard formula, I use =Sumproduct(C4:C8, D4:D8)/Sum(C4:C8), which gives a price of $13.52.

I tried to write a matching Query clause in D16 to calculate average sale price of item using: Query(B4:D8, "Select Sum(Col2*Col3)/Sum(Col2)").

However, this generates a "parsing" error" (see D18). Clearly, Summing product of 2 columns together then dividing by sum of one column requires a certain syntax. Are helper columns required? Any help with this one? Please note this has to be in a Query statement (Not SUMIF or Filter statements please). Here is the link to the sheet. https://docs.google/spreadsheets/d/e/2PACX-1vT_qwDiQxUF2wCSPS2ha_3fNgEPv85VNkzP99-MzcChpLL87oH0Pmbofy2-Zmj_o4w-b8JQ_TCZnsz8/pubhtml

Thank you all for your help. Badr

• google-sheets
• google-sheets-formula

• Apparently, the sum of products is not available in Google Sheets Query Formula. However, you may want to try this workaround wherein you will need to simplify the multiplication part outside of the query: =QUERY({ARRAYFORMULA(C4:C8*D4:D8),C4:C8},"SELECT SUM(Col1)/SUM(Col2)"). With this, I got 13.53461538 – PatrickdC Commented Mar 2 at 15:23
• By the way, you might want to present your sample data in a markdown table so that other community members may easily copy your data. You may create one with the help of this link. – PatrickdC Commented Mar 2 at 15:29
• Make sure to provide input and expected output as plain text table in the question. Check my answer or other options to create a table easily, which are easy to copy/paste. Avoid sharing links like spreadsheets, which make the question useless for others or images, which are hard to copy. Also, note that your email address can also be accessed by the public, if you share Google files. – TheMaster Commented Mar 2 at 16:22
• "Please note this has to be in a Query statement (Not SUMIF or Filter statements please)" — it is unclear where this requirement comes from. It seems quite arbitrary. – doubleunary Commented Mar 2 at 17:03
• 1 OK, so a Helper column it is. It worked. Thank you everybody. – Badr VA Commented Mar 3 at 11:51

2 Answers 2

Add a Helper Column

Apparently, there is no documentation about the sum of products feature for Google Sheets QUERY function. In your case, a helper column added to the input of the QUERY function is a good workaround. Simply create a helper column for the product of Column C and Column D. The formula should look like this:

=QUERY({ARRAYFORMULA(C4:C8*D4:D8),C4:C8},"SELECT SUM(Col1)/SUM(Col2)")

Input:

Output:

To remove the header, you may add the INDEX at the beginning of the formula:

=INDEX(QUERY({ARRAYFORMULA(C4:C8*D4:D8),C4:C8},"SELECT SUM(Col1)/SUM(Col2)"),2)

Wherein the output will be a single cell with the value of 13.53461538.

NOTE: If you want, you may opt to file a feature request to add a sum of products feature for the Google Sheets Query function using this link.

You can do that with query() like this:

=query( 
  query(B3:D, "select Col2 * Col3, Col2", 1), 
  "select sum(Col1) / sum(Col2)", 
  1
)

...but it's unclear why you'd need to use query() when there are easier ways to get the same result, for example with average.weighted(), like this:

=average.weighted(D4:D8, C4:C8)

See average.weighted().