Lookup with Many Criteria

[jontyoc]

HI

I have been given a data extract, and due to the criteria I have, the formula based on all of those finally works.
However, it is very heavy and slow - Calculating (4 Threads): XX%

The formula is as follows:

=SUMPRODUCT(--(INDEX(Extract!$2:$104857,,1)=E$2),--(INDEX(Extract!$2:$104857,,2)>=$D3),--(INDEX(Extract!$2:$104857,,2)<$D4),--(INDEX(Extract!$2:$104857,,4)=$F$1),--(INDEX(Extract!$2:$104857,,6)=$H$1),--(INDEX(Extract!$2:$104857,,7)=$J$1),--(INDEX(Extract!$2:$104857,,8)))

E2 is the date
D3/d4 is the time
F1 is the service area
H1 is the contact type (phone//email)
J1 is the inbound/outbound
column 8 is the value I am interested in.

It works perfectly for what I need, but the freeze, slow down drags for 10-15 minutes.
Is there anything that can be done to still look through all the 6/7 options and speed up the refresh?

Thanks.

 

[tyija1995]

Hey, try reference the column instead of the entire row, try:

=SUMPRODUCT(--(INDEX(Extract!A$2:A$104857,,1)=E$2),--(INDEX(Extract!B$2:B$104857,,1)>=$D3),--(INDEX(Extract!B$2:B$104857,,1)<$D4),--(INDEX(Extract!D$2:D$104857,,1)=$F$1),--(INDEX(Extract!F$2:F$104857,,1)=$H$1),--(INDEX(Extract!G$2:G$104857,,1)=$J$1),--(INDEX(Extract!H$2:H$104857,,1)))

 

[jontyoc]

THanks Tyija.

I thought this had worked as faster but still hanging. Is there anything more than I can do?

 

[tyija1995]

Sorry, that is the only changes I can think of that might have increased the speed of execution... It is a lot of data you're referencing so I can see why it runs pretty slowly.