?
Solved

Multiplying one column by the sum of many columns

Posted on 2011-09-16
10
Medium Priority
?
272 Views
Last Modified: 2012-05-12
In Excel 2007, how do I multiply a range D4:D17 by the sum of H4:L17?

Thanks!
0
Comment
Question by:Michael Paxton
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +1
10 Comments
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 36549379
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
 
LVL 50

Expert Comment

by:barry houdini
ID: 36549478
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
 
LVL 2

Expert Comment

by:jan24
ID: 36549494
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Michael Paxton
ID: 36549685
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
 
LVL 2

Expert Comment

by:jan24
ID: 36549811
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
 
LVL 2

Expert Comment

by:jan24
ID: 36549816
Sorry - try that without the dollars.
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 36549931
If you can put sums in, say, Column M, it becomes very easy:

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

Accepted Solution

by:
barry houdini earned 2000 total points
ID: 36549976
>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
 
LVL 50

Assisted Solution

by:barry houdini
barry houdini earned 2000 total points
ID: 36550810
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
 

Author Closing Comment

by:Michael Paxton
ID: 36551028
I implemented the first solution before the second solution was offered, but they both work.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question