# 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!
###### Who is Participating?

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

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

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

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

Process 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

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

Commented:
Sorry - try that without the dollars.
0

Commented:
If you can put sums in, say, Column M, it becomes very easy:

=SUMPRODUCT(d4:d17,m4:m17)
0

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

Process 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.