Multiplying one column by the sum of many columns

In Excel 2007, how do I multiply a range D4:D17 by the sum of H4:L17?

Thanks!
Michael PaxtonProcess EngineerAsked:
Who is Participating?
 
barry houdiniConnect With a Mentor Commented:
>I need a solution for (d14*sum(H14:L14))+(d15*sum(H15:L15))+(d16*sum(H16:L16))

For your original data ranges try

=SUMPRODUCT(D4:D17,SUBTOTAL(9,OFFSET(H4:L17,ROW(H4:H17)-ROW(H4),0,1)))

regards, barry
0
 
Patrick MatthewsCommented:
If you mean, multiply sum of range 1 by sum of range 2:

=SUM(d14:d17)*sum(h4:l17)

If that's not what you mean, then please claify.
0
 
barry houdiniCommented:
Taking that literally you seem to be asking for

=D4:D17*SUM(H4:L17)

that multiplies every cell in D4:D17 by the sum of the second range - the result is an array of 14 values which you'll need to array enter in a 14*1 range to see correctly......

reagrds, barry
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
jan24Commented:
If you mean you want to add (in column Z for example) what you get when each number in D is multiplied by the sum of H then use the following formula.  Write this in Z4:
= D4 * SUM($H$4:$I$17)
and then copy that formula down to the rest of column Z.

The $ signs will stop the reference changing as you copy it down.
0
 
Michael PaxtonProcess EngineerAuthor Commented:
Actually, I should have been clearer.  I need a solution for (d14*sum(H14:L14))+(d15*sum(H15:L15))+(d16*sum(H16:L16)) because my range is 25 rows and I have four worksheets.
0
 
jan24Commented:
How about, in Z4 you add a formula = D14*SUM($H$14:$L$14) and then copy it down.
Then take the sum of column Z.
0
 
jan24Commented:
Sorry - try that without the dollars.
0
 
Patrick MatthewsCommented:
If you can put sums in, say, Column M, it becomes very easy:

=SUMPRODUCT(d4:d17,m4:m17)
0
 
barry houdiniConnect With a Mentor Commented:
Actually the above might be overkill....

As long as the ranges don't contain any text then without any helper columns you can use just

=SUMPRODUCT(D4:D17*H4:L17)

regards, barry
0
 
Michael PaxtonProcess EngineerAuthor Commented:
I implemented the first solution before the second solution was offered, but they both work.
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.

All Courses

From novice to tech pro — start learning today.