Multiply two columns and add the products for a range of rows or cells?

How to multiply two columns and add the products (SUM()) even if the number or rows increases?

For example, given a spreadsheet like this:

 | A B
--------------
1 | 2 132.27
2 | 2 122.33
3 | 2 150.15
4 | 3 149.35

I need to know the total of multiplying column A with column B for each row and adding the result of every row.

Currently I am using a formula like this:

=(A2*B2+A3*B3+A4*B4+A5*B5+A6*B6+A7*B7+A8*B8)

The problem is that I need to keep updating the formula as I increase the number of rows.

Is there a better way to automate this in Excel?

1 Answer

This is exactly what SUMPRODUCT() is for:

=SUMPRODUCT(A2:A8,B2:B8)

SUMPRODUCT is an array type formula. Which means we do not want to use full column references, as it will do a lot of unnecessary calculations of empty cells.

So to make the Ranges increase(decrease) as the data set changes we can incorporate INDEX/MATCH to find the last cell in the columns with numbers:

=SUMPRODUCT(A2:INDEX(A:A,MATCH(1E+99,A:A)),B2:INDEX(B:B,MATCH(1E+99,A:A)))

This will now grow or shrink based on the data in columns A and B. It will set the ranges from A2 to the last cell in Column A with a number.