Solved

How to copy SUMPRODUCT on total lines

Posted on 2012-04-10
9
314 Views
Last Modified: 2012-05-06
I've attached a worksheet that has subtotals on it.  I need to, on each subtotal line, put in the following formula (in cell E4):
=SUMPRODUCT($D2:$D3,$E2:$E3/$D4)

THere are a LOT of subtotals on this sheet.  I've been doing a copy paste from one subtotal line to another but everytime I paste I need to make sure that SUMPRODUCT is picking up the right ranges because they need to adjust based on the number of rows above.  So, for example, in cell E12, the formula needs to be =SUMPRODUCT($D8:$D11,$E8:$E11/$D12) because there more lines of data involved in the subtotal.  I need to automate this because it will take me hours to do this via copy/paste.  I was copying, again for example, E7 to E12 and adjusting the numbers in the formula and then copying that formula to columns F, G, and H.  I'm thinking that I can look for the word "Total" in column A (I'll have to parse out the word using a string function) but then I'm at a loss on how to continue.  I would like to do this in VBA but if there's a quick way to do it otherwise I'd like to know that also.
Thanks
testsumproduct.xlsx
0
Comment
Question by:dkcoop03
9 Comments
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 37830970
Enter this formula on I2 =SUMPRODUCT(($A1:$A$2=$A1)*$D1:$D$2*I1:I$2)/$D
On I2 press ctrl-c
Select your data range (E2:H28)
Press F5
Click on special
Select Blanks and click on OK
now paste-special formulas.
0
 

Author Comment

by:dkcoop03
ID: 37832946
I'm not sure I understand your formula.  Do you mean enter the formula on I4?  I'm testing it a number of different ways but I'm not sure what you're doing.
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 37832956
No, I want you to copy the formula to I2. At this point it will give an error. Just follow the instructions above.
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 9

Expert Comment

by:anthonymellorfca
ID: 37833725
This was screaming "pivot table" at me, except for the division by totals - the solution is made up of calculated pivot table fields, so enter once, use forever. This is like entering the sumproduct formula for 1260 and all future entries are done for you for all results.

If there is a catch it is that the underlying data needs an extra (very simple " a x b ") multiplication "helper" column per heading to be "sum producted".
In fact the latest Excel table technology will auto append calculation columns when placed to the right of a table of entered data, so additional entry time can be nil.

Because of the way pivot tables do calcs using field totals, which usually breaks everything, in this case it works in favour of the solution by returning all items to their original values except the totals, which get divided by the CustCount total, just as is required.

Compare Pivot Results with original question results = match plus missing results filled in
Double click the screen shot image to download and view more clearly. It shows the original data side by side with my pivot table solution.

I will attach the underlying file..
written on an old mac in Excel 2008, should NOT make any difference.

Note: all columns headed " ... sp " are pivot table calculated fields, write once, use forever. Each one divides the desired sum by the total of Cust Count, and pleasantly derives the right answers, afaics.

Anthony


edit: I have to say ssaqibh's solution is very nice and exactly what was asked for, in my solution I'm questioning the use of sumproduct in the first place in favour of the separation of data storage and reporting.
testPivotTable.xlsx
0
 
LVL 59

Expert Comment

by:LeeTutor
ID: 37929486
I've requested that this question be deleted for the following reason:

Not enough information to confirm an answer.
0
 
LVL 43

Accepted Solution

by:
Saqib Husain, Syed earned 500 total points
ID: 37929487
I just realized when this delete request was put up that my formula had missed a digit at the end. I am attaching this digit now.

=SUMPRODUCT(($A1:$A$2=$A1)*$D1:$D$2*I1:I$2)/$D2

This works.
0
 
LVL 9

Expert Comment

by:anthonymellorfca
ID: 37929516
I think j ssaqb should get the points,  the  F5 special blanks idea is spot on and illustrates very well why I use EE.
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
Issue: Unstable cursor in Windows XP and Windows runs extremely slow in that any click will bring up the Hour glass (sometimes for several seconds before giving you what you want) . Troubleshooting Process and the FINAL FIX: This issue see…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
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…

910 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

19 Experts available now in Live!

Get 1:1 Help Now