Solved

Multiplying one column by the sum of many columns

Posted on 2011-09-16
10
261 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:paxtonm
Comment Utility
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
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 2

Expert Comment

by:jan24
Comment Utility
Sorry - try that without the dollars.
0
 
LVL 92

Expert Comment

by:Patrick Matthews
Comment Utility
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
Comment Utility
>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
Comment Utility
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
Comment Utility
I implemented the first solution before the second solution was offered, but they both work.
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

728 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

9 Experts available now in Live!

Get 1:1 Help Now