I’m working with a spreadsheet that has sales information across multiple columns. The key data I need includes salesperson name, quantity sold, and unit price in columns that I’ll call ColA, ColB, and ColC.
When I use the regular formula =SUMPRODUCT(B5:B9, C5:C9)/SUM(B5:B9) I get the correct weighted average of $13.52 for my dataset.
But I really need to accomplish this same calculation using a QUERY statement instead. I attempted this syntax: QUERY(A5:C9, "SELECT SUM(ColB*ColC)/SUM(ColB)") but it throws a parsing error.
It seems like doing multiplication of two columns and then division within a single QUERY formula needs special formatting. Do I need to create additional helper columns to make this work? What’s the proper way to structure this type of mathematical operation in a QUERY function?
I specifically need to use QUERY for this task rather than other functions like SUMIF or FILTER. Any suggestions would be really helpful!
Struggled with this for months before figuring out a workaround. You can’t do weighted averages directly in QUERY, but you can filter first with QUERY, then run your weighted average on those results. Try something like =SUMPRODUCT(QUERY(A5:C9,"SELECT ColB WHERE ColA IS NOT NULL"), QUERY(A5:C9,"SELECT ColC WHERE ColA IS NOT NULL"))/SUMIF(QUERY(A5:C9,"SELECT ColB WHERE ColA IS NOT NULL"),">0") when you need filtering plus calculations. Gets really useful when you add WHERE clauses to filter specific salespeople or dates before calculating. It’s verbose but keeps everything in one cell without helper columns. I mainly use it when I need dynamic filtering based on user inputs.
I’ve dealt with this headache countless times. Google Sheets QUERY just can’t handle complex calculations well, and wrestling with helper columns and nested queries is a time sink.
I used to waste hours trying to force QUERY to do everything until I found a better approach. You need proper automation that handles these calculations without spreadsheet function constraints.
I built automated workflows that pull data from Google Sheets, run weighted average calculations with real programming logic, then push results wherever I need them. No helper columns, no parsing errors, no workarounds.
The workflow reads your sales data, multiplies quantity by unit price for each row, sums those products, divides by total quantity, and spits out your weighted average. Takes 5 minutes to build and runs flawlessly.
This scales way better than fighting QUERY limitations. When your dataset grows or you need more complex calculations, just modify the workflow instead of rebuilding formulas.
Google Sheets QUERY function can’t handle math operations between columns directly in the SELECT clause. The syntax you tried won’t work - QUERY doesn’t support column multiplication like that.
You need a helper column first. Add a new column (ColD) with =B5*C5 and copy it down your range. Then your QUERY gets simple: =QUERY(A5:D9, "SELECT SUM(ColD)/SUM(ColB)"). This splits the multiplication from the QUERY operation.
I’ve hit this same wall tons of times trying to do complex calculations with QUERY. It’s great for filtering and grouping, but terrible with math across columns. Helper columns are honestly the only reliable way I’ve found to get weighted averages working with QUERY.
query just wasn’t designed for this stuff. I’ve tried it before and always go back to regular formulas - query gets wonky with calculations. If you’re set on using query, split it into two: one for the numerator, one for the denominator, then divide the results outside.
Hit this exact problem last year building a sales dashboard. QUERY’s SQL syntax just doesn’t handle arithmetic inside aggregate functions like you’d think it would. I solved it with ARRAYFORMULA + QUERY combo. Use =ARRAYFORMULA(QUERY({A5:A9, B5:B9, C5:C9, B5:B9*C5:C9}, "SELECT SUM(Col4)/SUM(Col2)")). The curly braces create a virtual array with your calculated column without cluttering your actual sheet. Gets you the weighted average in one formula while keeping QUERY. The array formula does the multiplication first, then QUERY processes it - no more parsing errors. Really handy when you want to add WHERE clauses later for filtering salespeople or dates.
The problem is QUERY treats math expressions like literal strings instead of actual operations. When you write SUM(ColB*ColC), it doesn’t recognize the multiplication inside the aggregate function. I figured out through trial and error that array formulas work better with QUERY for filtering. Try =SUMPRODUCT(QUERY(A5:C9, "SELECT ColB"), QUERY(A5:C9, "SELECT ColC"))/SUM(QUERY(A5:C9, "SELECT ColB")). This separates column selection from the math operations. You could use nested queries, but they get slow with bigger datasets. QUERY’s great for filtering data, but forcing complex math through it usually creates more headaches. The helper column approach someone mentioned earlier is still the easiest way to handle weighted averages with QUERY.