Solved

Multiplying one column by the sum of many columns

Posted on 2011-09-16
10
262 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:paxtonm
  • 3
  • 3
  • 2
  • +1
10 Comments
 
LVL 92

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
Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

 

Author Comment

by:paxtonm
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 92

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 500 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 500 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:paxtonm
ID: 36551028
I implemented the first solution before the second solution was offered, but they both work.
0

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

813 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now