I'm trying to process a couple of values in Excel, and came up with this formula, which works: =$A3*B3+$A4*B4+$A5*B5+$A6*B6+$A7*B7+$A8*B8+$A9*B9+$A10*B10+$A11*B11However it's very long and I feel there is a better way to perform such a simple operation. (basically multiplying two values on each column, then moving down one row and performing the same operation until row 11, adding up the products of each calculation.)

Don't worry if plan A fails, there are 25 more letters in the alphabet ;)

message edited by RainBawZ

Hi RainBawz Are you able to use column C to hold the values of A*B?

In C3 you will then have '=A3*B3'

if you were able to do that then all you would need to do is add all of column C from C3 to C11. Or even have it sum it for you at the bottom.

Other than that all I can offer is a VBA solution not sure this is what you want but its always good to have options.

Sub DoCalc() Dim iMult As Long Dim iSum As Long iMult = 0 iSum = 0 For Each bcell In Range("A3:A11") iMult = bcell * bcell.Offset(0, 1) iSum = iSum + iMult Next bcell Debug.Print iSum End Sub

How about this formula...

=SUMPRODUCT(A3:A11*B3:B11)

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Orrrr Derbys suggestion lol

Ask Your Question

Weekly Poll