• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Sumproduct - super slow

P

pr4peace

New Member
Hi ,


Long Question alert !!


I need help on speeding up the sumproduct function in my company sheet.


This is the scenario. This is basically for a Daily Progress Report for a construction site.(dpr)


1. I have set up the collection sheet where all engineers would enter that day's activities and qty performed in 8 different clusters(mini projects)


2. The management needs to get a DPR based on that.


3. the dpr needs to be categorised in different activities and in the 8 different clusters.


4. They need the qty achieved and the cumulative qty everyday.


5. I tried doing this using the Sumproduct function.


Format of Collection sheet

-----------------------------------

Date | Activity | Location | Achieved Qty|


Format of DPR Sheet

--------------------

DPR Date:12 mar 2012 (or other)

Cumulative Date: 12 mar 2012 (or other)


Location | Item | Excavation| PCC |Etc

--------------------------------------------------------

A |Achieved | 100 |

|Cumulative | 2000 |

---------------------------------------------------------

B

---------------------------------------------------------

C

D


For calulating Achieved, I used the collection sheet


=SUMPRODUCT(1*('Excavation'=Activity Column'),1*('A'=Location Column),(1*(DPR Date =Date Column)),(Qty Column))


For Calculating Cumulative , I made another sheet with format

Exc PCC Etc

A formula

B

C


formula


=SUMPRODUCT(1*('Excavation'=Activity Column),1*('A'=Location Column),(1*(DPR Date >= Date Column)),(Qty Column))


Problem


1. Formuala is tooo slow.

2. Can we get the achieved qty between 2 dates?


Sorry for the long question?


Thanks in Advance


Bye,

Prashanth
 
Hi Faseeh and Srinidhi,


Sorry for the late reply.


Please find my workbook in this link


http://goo.gl/x6CPp


Thanks

Prashanth
 
Hi Prashanth ,


Please give everyone access to your file ; otherwise , each person has to request you for permission to access the file.


Narayan
 
Sorry again,


Had no idea, it would ask for Account access. Sorry for all the inconvenience caused.


This should work.

http://dl.dropbox.com/u/23746991/DPR.xls


Thanks ,

Prashanth
 
Hi Prashanth ,


Thanks for uploading the workbook.


I do not understand the actual structure of your workbook ; the tab labelled "Data Sheet" has a lot of data which appears to be manually entered ; the tab labelled "Actuals Sheet" has the same data arrived at through a very long formula ; what is actually happening here ? Can you clarify the following :


1. Which sheet has the raw data ?


2. Which sheet has the base formulae ?


3. Which sheet has the dependent formulae ?


Are you having too many SUMPRODUCT formulae , many of which may not be necessary ?


Narayan
 
Hi Narayan,


Thanks for the quick reply.


I am sorry for having confusing data.


Let me start by going through the process again.


1. The DPR-Collection Sheet is the physical handout given to Engineers at ground. Has no relevance in any of the calculatons.


2. The DPR - Log sheet collects all data from the hard copy, hence having the same structure as the DPR-Collection sheet. This collects all raw data for computation.


3. The Data Sheet actually does have manual entered data. It basically states the scope of work for each of the activity in the project . This is done by the estimator. He/She manually enters that data. This is used to mention the complete scope of the activity to calculate % complete in the DPR sheet.


4. The scope sheet is redundant , my bad for not cleaning up the sheet before uploading.


5.The Actuals Sheet is the sheet which consolidates all data from DPR-Log sheet entered by the engineers. The problem in my sheet is the actual data matched the complete scope of the activity since the activity is 100% completed hence the confusion(again sorry). In a day to day case the actual would be well lower than the scope. So the Actuals sheet only consolidates data upto the current date(useful to calculate the cumulative quantity).


6. The DPR sheet is the final report, which collects data from all above mentioned sheets.


I dont know if I have just over-complicated my life! :)


Thanks in advance!!


Bye

Prashanth
 
Just to add another point.


I originally used Pivot to consolidate , the limitation I found in that it was not allowing me to generate a DPR based on a manually entered date.


While Pivot allowed me to filter to a specified date , I was not able to link that required date data to another cell.


In sumproduct , it allowed me choose the date in which I wanted the DPR and also for cumulative qty, i could choose all dates before the DPR date as one of the array components.


I guess this is my basic problem. :)


Sorry


Thanks,

Prashanth
 
Hi Prashanth ,


Thanks for clarifying in detail ; now let me summarise !


1. The tab labelled "Actuals Sheet" has the raw data , as far as preparing the report is concerned.


2. The tab labelled "DPR" is the report. All the formulae that you want are to be entered in this tab.


If the above are correct , then if there are other formulae anywhere else in the workbook , they should , as far as possible , borrow from the results available in the "DPR" tab.


Narayan
 
Hi Narayan,


Another blooper, the Actuals sheet makes no sense whatsoever, I could have put that formula directly into the DPR(in the cumulative column). Silly mistake as I had originally modified the Pivot into a Sum-product hence an additional sheet was created, I have deleted the Actuals Sheet now.


But the sheet is still slow. The layout seems more optimized thanks to you but the speed is still slow.


Thanks again


Prashanth
 
Hi Prasanth ,


We'll get there !


Can you upload your revised workbook ? Or do I go through the earlier uploaded one , ignoring the tab labelled "Actuals Sheet" ?


Narayan
 
Hi Prasanth ,


Just a first look shows one problem - all your SUMPRODUCT formulae refer to cells up to 10001 , whereas data actually resides only till row 75 or so ; can you not reduce all these formulae to refer to the maximum possible row , say 500 or so ?


Narayan
 
Hi Narayan,


I dont think so, the DPR-Log sheet will be updated everyday.Almost 10-15 lines of entry would be added everyday. So the data would be huge within 3-4 months, so the sumproduct reference has to be able to scale that.


Thanks

Prashanth
 
Hi Prasanth ,


Allow the formulae to scale up as the data grows ; use named ranges so that at least as long as the data is small , the calculations don't take up too much time.


Even otherwise , 15 lines of entry , even after 3 months , will be only around 1500 rows ; surely 10000 is a long way off.


Narayan
 
Hi Narayyan & pr4peace,


The data in the sheet provided has not been large so much so that it cause this problem, I regularly work with a file that record stores consumption for an entire month and that contains data of last 12 months so some 4000+ rows and that does not take so much time either. This file caused my system to hang almost!! I think there is some 'intrinsic problem' with this file.


I have worked out fictitious data in a similar table, started from scrap and did't copied a single thing from the sample file, See how smoothly it is working. I also found out at following link that sumifs() work faster then sumproduct(), in case pr4pace is using excel 2007 it can be used. Option for date has also been incorporated.


Kindly see this file: http://dl.dropbox.com/u/60644346/New%20Workout_Sumifs%20Vs%20Sunproduct.xlsx


... and here is the link where i found about computation speeds of sumifs vs sumproduct:


http://www.mrexcel.com/forum/showthread.php?t=436279


Regards,

Faseeh
 
Hi Prashanth ,


I have uploaded the worksheet ; all I did was modify the SUMPRODUCT formulae on the "Actuals Sheet" a little. I have also deleted all the SUMPRODUCT formulae in the Achieved cells on the "DPR" tab. You can either put them in yourself , or let me know how this is to be calculated.


https://docs.google.com/spreadsheet/ccc?key=0AkKMpuzr3MTVdGNZRWJyNFA0OVdidHVSNVAwVHpsRVE


Narayan
 
Hi Narayan and Faseeh,


Firstly the support has been amazing !!!! Thanks so much.


The sumifs worked beautifully. I had some glitches then later realized the sumifs work only if all the ranges are the same. Named ranges also helped me make the formula look decent.


Thank you so much guys.
 
You must log in or register to reply here.
Back
Top