Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question
1  
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
add comment

2 Answers

up vote 2 down vote accepted

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!

share|improve this answer
    
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
add comment

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

share|improve this answer
    
this works and array function is a simple solution, but both work –  datatoo Mar 3 '11 at 17:37
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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