Sumproduct() - Need for speed or change

[ShagNasty]

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="030")),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="030")),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="030")),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="030")),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$10000)
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$10000)
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$10000)
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$10000)
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..

 

[broro183]

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