Is there a multiple-and-add formula in Google's spreadsheet?

Question

What I want is to easily multiply a number by another number for each column and add them up at the end in Google Sheets. For example:

User | Points 1 | Points 2 | Points 3 | Total
     |     5    |     1    |     4    |
-----+----------+----------+----------+------
Jane |        2 |        3 |        0 |   13 (2*5 + 3*1 + 0*4)
John |        1 |       11 |        4 |   32 (1*5 + 11*1 + 4*4)

So it's easy enough to make this formula for the total:

= B3*$B$2 + C3*$C$2 + D3*$D$2

The problem is I frequently need to insert additional columns or even remove some columns. So then I have to mess with all the formulas. It's a pain... we have many spreadsheets with these formulas. I wish there was a formula like SUM(B3:D3) where I could just specify a range. Is there anything like MULTIPLY_AND_SUM(B2:D2, B3:D3) that would do this? Then I could insert columns in the middle and the range would still work.

Answer 1

There is a built in function in Google Sheets that does exactly what you are looking for: SUMPRODUCT. In your example the formula would be:

=sumproduct(B$2:D$2,B3:D3)

Click here for more information about this function.