CODEWITHSUNDEEP
excelperformanceoptimization
Neha Bansal-Singhal
Neha Bansal-Singhal

Reputation: 3

Excel formula sumproduct needs optimization

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

Upvotes: 0

Views: 242

Answers (1)

Scott Craner
Scott Craner

Reputation: 152660

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

Upvotes: 2

Related Questions