# Sumproduct needs optimization

#### nehabansal

##### New Member
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

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

#### Ron Coderre

##### MrExcel MVP
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?

Replies
1
Views
55
Replies
0
Views
248
Replies
1
Views
296
Replies
0
Views
894
Replies
0
Views
150