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

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.

-
Guess you can't make another column that is F*B and just sum it up at the bottom? – Ivo Flipse Feb 9 '10 at 18:43

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!

-
Hi, thanks. I did think of this but was trying to avoid the extra column. The spreadsheet will become more complex over time so i'd like to keep the formula/layout simple. – Fermin Feb 9 '10 at 18:46
Well I'm not sure if there is much that can be done... let me think – Urda Feb 9 '10 at 19:06
@Fermim, I have a new function, it looks like this oneliner should do it for you, just change the arrays in question to your quantity and profit cells – Urda Feb 9 '10 at 19:12

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

-
this works and array function is a simple solution, but both work – datatoo Mar 3 '11 at 17:37