I’m working with a spreadsheet that has multiple columns but I need to focus on three specific ones: product name, quantity sold, and unit price. I want to calculate the weighted average price using the Query function.
When I use the regular formula =SUMPRODUCT(B5:B9, C5:C9)/SUM(B5:B9) it works fine and gives me the correct result of around $13.50.
But when I try to do the same calculation with a Query statement like =QUERY(A5:C9, "SELECT SUM(Col2*Col3)/SUM(Col2)"), I keep getting a parsing error.
I think the issue is with how I’m trying to multiply columns inside the SUM function and then divide by another SUM. Is there a special way to write this in Query syntax? Do I need to create additional helper columns to make this work?
I really need to solve this using Query function specifically, not other functions like SUMIF or FILTER. Any suggestions on the correct syntax?
Had this exact problem six months ago building a sales dashboard. Google Sheets Query handles arithmetic way differently than regular formulas. You can’t do multiplication inside SUM like SUM(Col2*Col3) - it just doesn’t work. I tried =QUERY(A5:C9, "SELECT SUM(Col2*Col3), SUM(Col2)") to get both values then divide them outside the query. Sometimes worked, sometimes didn’t depending on the Sheets version. What actually fixed it: I added a helper column that does the multiplication first, then queried that column instead. Not as clean as one formula, but it works every time with Query’s limitations.
yeah, query’s pretty limited for calculations like this. i usually wrap it in arrayformula and do the math outside - something like =arrayformula(sum(query(A5:C9,"select col2*col3"))/sum(query(A5:C9,"select col2"))) might work. but honestly? just use sumproduct for weighted averages. query wasn’t built for this.
The parsing error occurs because the Query function does not allow for arithmetic operations within aggregate functions like SUM(Col2*Col3). To achieve your goal, first use =QUERY(A5:C9, "SELECT Col1, Col2, Col3") to retrieve your data. You can then apply SUMPRODUCT to calculate the weighted average from that result. Alternatively, if you prefer to stick with the Query function, you should create a helper column to multiply Col2 and Col3 before querying and summing that new column. Keep in mind that Query is more suitable for filtering and grouping data rather than performing complex calculations.