nehabansal

New Member
Joined
Jun 27, 2018
Messages
1
Hi,
I am using SUMPRODUCT to summarize the vacation days for all employees for each month. My formula looks like this:
=SUMPRODUCT(
('Cal'!$A$1:$A$200=Summary!$A1)*
(('Cal'!$D$4:$ND$160="VC")+
('Cal'!$D$4:$ND$160="SK")+
('Cal'!$D$4:$ND$160="TR")+
('Cal'!$D$4:$ND$160="JD")+
('Cal'!$D$4:$ND$160="BR")+
('Cal'!$D$4:$ND$160="LOA")+
(('Cal'!$D$4:$ND$160="HVC")/2)+
(('Cal'!$D$4:$ND$160="HSK")/2)+
(('Cal'!$D$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
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Ron Coderre

MrExcel MVP
Joined
Jan 14, 2009
Messages
2,316
I could use a few more details....but....I used this example:
On the Summar sheet
A1: a name to match
C1:C9 contains this list:
Code:
VC
SK
TR
JD
BR
LOA
HVC
HSK
HTR
E4: the date range start date
F4: the date range end date

and...on the Cal sheet
D3:ND3 contains the date range Jan-1 through Dec31
A4:A200 contains names

This regular formula returns the count of listed day-type abbreviations for the person
Code:
=SUMPRODUCT(COUNTIF(
INDEX(INDEX(Cal!$D$4:$ND$203,MATCH(Summary!A1,Cal!$A$4:$A$200,0),0),MATCH(E4,Cal!$D$3:$ND$3,0))
:INDEX(INDEX(Cal!$D$4:$ND$203,MATCH(Summary!A1,Cal!$A$4:$A$200,0),0),MATCH(F4,Cal!$D$3:$ND$3,0)),$C$1:$C$9))
Is that something you can work with?
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,167,063
Messages
5,851,872
Members
431,470
Latest member
mikaelaleksandrowicz

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top