x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 214

# 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
0
mike637
• 2
• 2
1 Solution

Commented:
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

Author 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

Commented:
OK, so use this in D57:

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

Then copy that across through J57 if you like.
0

Author Commented:
Hi Matthewspatrick:

Thank you - worked perfectly!

Michael
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.