CODEWITHSUNDEEP
google-sheets
Neill
Neill

Reputation: 452

Query the sum of a product

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)

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!

Upvotes: 4

Views: 5098

Answers (2)

Max Makhrov
Max Makhrov

Reputation: 18717

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")

Upvotes: 10

aaron
aaron

Reputation: 43098

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)

Upvotes: 7

Related Questions