Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sumproduct() - Need for speed or change
This is probably a simple routine for you people unless my verbiage is to confusing, but here goes... The following code works correctly, however, I am experiencing performance flashbacks to the 80's using a superfast PC (4 MHz maybe). My question, Is there a better way to do the calculations below? The worksheets takes more than several minutes to recalculate. Programming, outside normal day-2-day functions, is like my golf game €“ I know how to hunt lost balls and rake sand traps€¦ I tried to supply the structure of the two worksheets below that I'm using. Worksheet JCodes A2 Job Code B2 Job Desc C2 =SUMPRODUCT(--(PP09!$D$2:$D$10000=$A2)*(LEFT(PP09!$L$2:$L$10000, 1)="3"),--((PP09!$F$2:$F$10000="017")+(PP09!$F$2:$F$10000="0 30")),PP09!$I$2:$I$10000) D2 =SUMPRODUCT(--(PP09!$D$2:$D$10000=$A2)*(LEFT(PP09!$L$2:$L$10000, 1)="3"),--((PP09!$F$2:$F$10000="017")+(PP09!$F$2:$F$10000="0 30")),PP09!$H$2:$H$10000) E2 =SUMPRODUCT(--(PP09!$D$2:$D$10000=$A2)*(LEFT(PP09!$L$2:$L$10000, 1)="3")* (PP09!$F$2:$F$10000="012"),PP09!$I$2:$I$10000) F2 =SUMPRODUCT(--(PP09!$D$2:$D$10000=$A2)*(LEFT(PP09!$L$2:$L$10000, 1)="3")* (PP09!$F$2:$F$10000="012"),PP09!$H$2:$H$10000) G2 =SUMPRODUCT(--(PP09!$D$2:$D$10000=$A2)*(LEFT(PP09!$L$2:$L$10000, 1)="5"),--((PP09!$F$2:$F$10000="017")+(PP09!$F$2:$F$10000="0 30")),PP09!$I$2:$I$10000) H2 =SUMPRODUCT(--(PP09!$D$2:$D$10000=$A2)*(LEFT(PP09!$L$2:$L$10000, 1)="5"),--((PP09!$F$2:$F$10000="017")+(PP09!$F$2:$F$10000="0 30")),PP09!$H$2:$H$10000) I2 =SUMPRODUCT(--(PP09!$D$2:$D$10000=$A2)*(LEFT(PP09!$L$2:$L$10000, 1)="5")* (PP09!$F$2:$F$10000="012"),PP09!$I$2:$I$10000) J2 =SUMPRODUCT(--(PP09!$D$2:$D$10000=$A2)*(LEFT(PP09!$L$2:$L$10000, 1)="5")* (PP09!$F$2:$F$10000="012"),PP09!$H$2:$H$10000) K2 =SUMPRODUCT(--(PP09!$D$2:$D$10000=$A2)*(LEFT(PP09!$L$2:$L$10000, 1)="3"),--(LEFT(PP09!$F$2:$F$10000,1)="6")+(LEFT(PP09!$F$2:$ F$10000,1)="7"),PP09!$I$2:$I$10000) L2 =SUMPRODUCT(--(PP09!$D$2:$D$10000=$A2)*(LEFT(PP09!$L$2:$L$10000, 1)="3"),--(LEFT(PP09!$F$2:$F$10000,1)="6")+(LEFT(PP09!$F$2:$ F$10000,1)="7"),PP09!$H$2:$H$10000) M2 =SUMPRODUCT(--(PP09!$D$2:$D$10000=$A2)*(LEFT(PP09!$L$2:$L$10000, 1)="5"),--(LEFT(PP09!$F$2:$F$10000,1)="6")+(LEFT(PP09!$F$2:$ F$10000,1)="7"),PP09!$I$2:$I$10000) N2 =SUMPRODUCT(--(PP09!$D$2:$D$10000=$A2)*(LEFT(PP09!$L$2:$L$10000, 1)="5"),--(LEFT(PP09!$F$2:$F$10000,1)="6")+(LEFT(PP09!$F$2:$ F$10000,1)="7"),PP09!$H$2:$H$10000) O2 =SUMPRODUCT(--(PP09!$D$2:$D$10000=$A2)*(LEFT(PP09!$L$2:$L$10000, 1)="3" )*(LEFT(PP09!$F$2:$F$10000,1)="8"),PP09!$I$2:$I$10 000) P2 =SUMPRODUCT(--(PP09!$D$2:$D$10000=$A2)*(LEFT(PP09!$L$2:$L$10000, 1)="3")* (LEFT(PP09!$F$2:$F$10000,1)="8"),PP09!$H$2:$H$1000 0) Q2 =SUMPRODUCT(--(PP09!$D$2:$D$10000=$A2)*(LEFT(PP09!$L$2:$L$10000, 1)="5")* (LEFT(PP09!$F$2:$F$10000,1)="8"),PP09!$I$2:$I$1000 0) R2 =SUMPRODUCT(--(PP09!$D$2:$D$10000=$A2)*(LEFT(PP09!$L$2:$L$10000, 1)="5")* (LEFT(PP09!$F$2:$F$10000,1)="8"),PP09!$H$2:$H$1000 0) S2 =SUM(C2,E2,G2,I2,K2,M2,O2,Q2) T2 =SUM(D2,F2,H2,J2,L2,N2,P2,R2) A B C D E F G H I J K L M N O P Q R S T JobCode JobDesc Cot$ CotHrs Cest$ CestHrs BSot$ BlotHrs Blest$ BlestHrs 12h$ 12hHrs 12h$ 12hHrs Cbs$ CbsHrs BLbs$ BLbsHrs Sum$ SumHrs 1 67 Worksheet JCodes is a breakdown on pay for each Job Code (66), outside of normal bi-weekly straight time salary, i.e. - OT, Capital, O&M. Worksheet PP09 EmpID EName LDescr JCode JTitle ECode EDescr Hrs Pay PPEnd RCode TERC Worksheet PP09 contains YTD payperiod information (presently 5555 records on ~400 employees). This is where the data for JCode originates. Thanks as always -- but please don't waste your time on this if it's to time consuming or confusing. Shag.. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sumproduct() - Need for speed or change
hi Shag, I have the below suggestions, without going to the effort of building a spreadsheet - are you able to upload a sample file? - Limit the sumproduct array size to only include the rows with data - try using dynamic named ranges. - Have you considered using a Pivot Table (see 'here ' (http://peltiertech.com/Excel/Pivots/pivotstart.htm)for Debra Dalgleish's intro)? - Try to remove any duplicate calculations from being repeated in each column by separating them into a separate column so they are only called once. - You may also find some useful info on the following links of Charles Williams & Bob Phillips: 'http://www.decisionmodels.com/optspeedb.htm (also see other frames listed at top of screen)' (http://www.decisionmodels.com/optspeedb.htm) http://www.xldynamic.com/source/xld.SUMPRODUCT.html hth Rob -- broro183 Rob Brockett. Always learning & the best way to learn is to experience... ------------------------------------------------------------------------ broro183's Profile: http://www.thecodecage.com/forumz/member.php?userid=333 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=109125 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct against worksheet vs named range- any speed difference? | Excel Worksheet Functions | |||
Sumproduct and speed of Calc. | Excel Worksheet Functions | |||
How do I speed up recalcs when using lots of SUMPRODUCT formulas | Excel Worksheet Functions | |||
sumproduct, arrays affecting speed | Excel Worksheet Functions | |||
How do I change scroll speed? | Setting up and Configuration of Excel |