Sumproduct needs optimization

[nehabansal82]

Hi,
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

[jason.b75]

The non numeric values are not stopping you from using sumifs (although countifs would be the appropriate choice), but the division by 2 where records are equal to HVC,HSK or HTR do stop you from using it with a little offset trickery.

Are D3:ND3 days of the year in ascending order?

If so then setting up a dynamic named range could help.

Create a named range that refers to

=INDEX(Cal!$D$4:$ND$160,MATCH($E$4,Cal!$D$3:$ND$3)):INDEX(Cal!$D$4:$ND$160,MATCH($F$4,Cal!$D$3:$ND$3))

Note that the 2 ranges highlighted in red will need to be qualified with the relevant sheet name from your workbook.

Once this is none, edit your existing formula to replace each occurrence of the range 'Cal'!$D$4:$ND$160 with the new name.

Doing this will mean that the formula is not caluclauting 335 unnecessary columns of data if the dates in E4 and F4 only span 30 days.

If you have empty rows at the bottom you could further improve this by making the last row dynamic as well.

Edit:-

Was having a bit of trouble getting my thoughts on order on this one, see if this produces the correct results, it's volatile so will potentially need to recalculate more often than sumproduct, but if it works it might be worth a try.

=SUM(COUNTIFS(Cal!$A$4:$A$160,Summary!$A1,OFFSET(Cal!$D$4:$D$160,0,COLUMN($D$4:$ND$4)-COLUMN($D$4)),{"VC","SK","TR","JD","BR","LOA"}),
COUNTIFS(Cal!$A$4:$A$160,Summary!$A1,OFFSET(Cal!$D$4:$D$160,0,COLUMN($D$4:$ND$4)-COLUMN($D$4)),{"HVC","HSK","HTR"})/2)

[nehabansal82]

Thanks Jason. I tried the formula however it always returns 0.

[Sam Capricci]

This could work, I've used the sumproduct in a similar way but would have to test it...

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

edited, adjusted to add parens and multiplier - unable to test

[jason.b75]

Could you describe your sheet in a bit more detail.

From what I can see, this is a holiday planner, Cal!A4:A160 contain employee id of some kind (no duplicates), D3:ND3 Jan 1 to Dec 31, D4:ND4 codes to identify absense type, with the last 3, HVC, HSK, HTR denoting 1/2 day.

E4 and F4 contain the start and end dates for the month / period of interest.

Summary!B8 contains the employee id of interest.

Just noticed that you edited your original post, I didnt refresh it so 1 of the ranges was wrong in my formula.

It should have been

=SUM(COUNTIFS(Cal!$A$4:$A$160,Summary!$B8,OFFSET(Cal!$D$4:$D$160,0,COLUMN($D$4:$ND$4)-COLUMN($D$4)),{"VC","SK","TR","JD","BR","LOA"}),
COUNTIFS(Cal!$A$4:$A$160,Summary!$B8,OFFSET(Cal!$D$4:$D$160,0,COLUMN($D$4:$ND$4)-COLUMN($D$4)),{"HVC","HSK","HTR"})/2)

However, if my analysis of your sheet is correct then a much simpler approach could be used. Did I miss anything?

Based on the above assumptions.

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

Both formulas are untested.

[macropod]

Cross-posted at: https://www.mrexcel.com/forum/excel-...st5095516.html
Please read Excel Forum's Cross-Posting policy in rule 8: http://www.excelforum.com/forum-rule...rum-rules.html