Excel - calculation for 50 cells w/o VBA coding it

Hello Experts,

I wanted to see if there is a way to write an Excel Formula that will calculate a large set of numbers without having to type it all out.  I am trying to avoid using VBA coding, if possible.  But will resort to it if needed.

Range("C57") = sum("D6*$L$6)+(D7*$L$7) this continues to row 50.  It is always column D * L.
Is there a way to write this as a function instead of typing 45 steps?

Otherwise I need to set it as a change cell event for range (D6:I50).

If I can be helped with both of these scenerios - it would be great.

Thank you,
Michael
mike637Asked:
Who is Participating?
 
Patrick MatthewsConnect With a Mentor Commented:
OK, so use this in D57:

=SUMPRODUCT(D6:D55,$L$6:$L$55)

Then copy that across through J57 if you like.
0
 
Patrick MatthewsCommented:
If you mean that C57 should have:

=SUMPRODUCT(D6:D7,L6:L7)

and C100 should have:

=SUMPRODUCT(D49:D50,L49:L50)

then simply enter that first formula into C57, and copy it down through C100.

If you meant to fix the references to Col L, then use this instead:

=SUMPRODUCT(D6:D7,$L$6:$L$7)
0
 
mike637Author Commented:
Hi Matt,

Not exactly,

There is a column of numbers in column D (starting at row 6)  I need to mulitpy D6 by L6, and D7*L7 and, D8*L8 and D9*L9 and so on until I reach row D55*L55. All of this needs to be contained in Cell "D57" to calc this whole process.

I am attaching a sheet with an example.

I have to use this formula in 35 other instances in the same sheet with different different rows and columns.  But if I can get the first example down to a simplified formula, then I can use that in the other instances.

I wanted to see if there was a way to write it so I did not have to type out all 50 expressions.

Thanks,
Michael
Book1.xlsx
0
 
mike637Author Commented:
Hi Matthewspatrick:

Thank you - worked perfectly!

Michael
0
All Courses

From novice to tech pro — start learning today.