Query the sum of a product

Question

I want to write a query that returns the sum of two multiplied cells, where the name is equal to a person's name. Let's say I say I have:

| Name | score | days|
|---------|------|------|
| Suzie | 5.0 | 3
| Jonny | 5.0 | 1
| Suzie | 4.0 | 1
| Suzie | 5.0 | 7
| Jonny | 4.0 | 1

basically, I want to know Suzie's average score. Some rows represent multiple days though. The arithmetic would be

(5.0 * 3) + (4.0 * 1) + (5.0 * 7)

I want to write a query:

=(index(query({a1:c3}, "Select Sum(Col2 * Col3) where Col1 = '" & D22 & "'"),2))

However, it doesn't like the part that says "Sum(Col2 * Col3)"

Is it possible to do the sum of two columns multiplied?

Thank you in advance!

user_5372400 · Answer

Please see this sample file.

Data

Col1    Col2    Col3
a         10     20
a         30     40
b         10     30

Result

a   1400
b   300

Formula

=QUERY({A2:A,ARRAYFORMULA(B2:B*C2:C)}, "select Col1, sum(Col2) where Col1 <> '' group by Col1")

user_8601760 · Answer

You don't need a query.

sum of two multiplied cells, where the name is equal to a person's name

Use SUMPRODUCT(array1, [array2, ...]):

=SUMPRODUCT(B1:B5,C1:C5,A1:A5=D22)

Notice that A1:A5=D22 is a criterion.

average score

Use SUMIF(range, criterion, [sum_range]):

=SUMPRODUCT(B1:B5,C1:C5,A1:A5=D22)/SUMIF(A1:A5,D22,C1:C5)