Excel formula sumproduct needs optimization

0
votes

I am using SUMPRODUCT to summarize the vacation days for all employees for each month. My formula looks like this: 

       =SUMPRODUCT((Cal!$B$4:$B$160=Summary!$B8)*
       ((Cal!$C$4:$ND$160="VC")+
       (Cal!$C$4:$ND$160="SK")+
       (Cal!$C$4:$ND$160="TR")+
       ((Cal!$C$4:$ND$160="HVC")/2)+
       ((Cal!$C$4:$ND$160="HSK")/2)+
       ((Cal!$C$4:$ND$160="HTR")/2))*
       (Cal!$D$3:$ND$3>=$E$4)*
       (Cal!$D$3:$ND$3<=$F$4))

The vacation days are entered by the employees in sheet Cal and the formula is in sheet Summary. Both sheets are part of the same workbook.

This formula runs for number of employees * months in the year and has slowed down my excel sheet considerably. Can you please suggest how can I optimize it? P.S. I cannot use SUMIFS as the values I am counting - VC, SK, etc. are non-numeric

1
excelperformanceoptimization
is the only one employee per row, and no duplicates? - Scott Craner
@ScottCraner Can't you use { } to create an array of values to consider here? Or is that only for SUMIF? - urdearboy
@urdearboy yes and if we can narrow the row we can use a couple COUNTIFS() to do what is wanted. - Scott Craner
I'm not even sur I know how that works since the lateral calculation cycle is between Cal!C:ND in one section and between Cal!D:ND in another. Mismatched calculation ranges tend to throw #VALUE! errors. - user4039065
@Jeeped count the wholes twice and divide the result by 2. - Scott Craner

1 Answers

2
votes

Use

=SUM(COUNTIFS(Cal!$C$3:$ND$3,">=" & $E$4,Cal!$C$3:$ND$3,"<=" & $F$4,INDEX(Cal!C:ND,MATCH(Summary!$B8,Cal!B:B,0),0),{"VC","SK","TR","VC","SK","TR","HVC","HSK","HTR"}))/2