How can I write an excel formula to do row based calculations?

Question

Could someone please help me with the following excel formula. Each row in my spreadsheet contains details about a product.

I have one column 'Quantity' (Col B) and 'Profit' (Col F). I want to use a formula which takes the Quantity * Profit for each row and gives me the total.

So far I have

=SUM((F2*B2)+(F3*B3)+(F4*B4)+(F5*B5)+(F6*B6)......)

But this will get unwieldy very quickly. Is there a better way of writing this? I have thought of making a hidden column containing F*B for each row and summing them but would like to avoid this unnecessary column.

Answer 1

First I make a third coloumn which will hold our (ColB)*(ColF) Value. Say column H will hold our value...

=SUM(B[row#]*C[row#])

Which you can thing drag down all the way to the end, by grabbing the bottom right corner of the box. This will produce values for all rows. Now we can just sum the results...

=SUM(H[first row #]:H[last row #])

When you add another row, just make sure you drag your equation down one more row, and increase the end pointer of the 2nd =SUM function.

---

EDIT:

SumProduct is what you need...

=SUMPRODUCT((B2:B11),(C2:C11))

SUMPRODUCT - Multiplies corresponding components in the given arrays, and returns the sum of those products.

Basically it will take, one by one of course, Array 1 Cell 1 * Array 2 Cell 1, and add the result up.

That should do it!

Answer 2

You could use SUM function in matrix mode (which allows you to apply operations between ranges instead of single cells). For example, let say you want to compute the sum of products between values in range B2:B10 with values in range F2:F10. Then Use:

=SUM(B2:B10*F2:F10)

and confirm with Ctrl+Shift+Enter