1

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?

2

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.

  • Nice, this is exactly what I was looking for. Thx – kiewic Jun 2 '17 at 16:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.