Lookup with Many Criteria

jontyoc

Board Regular
Joined
Sep 3, 2012
Messages
52
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.
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
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)))
 
Upvote 0
THanks Tyija.

I thought this had worked as faster but still hanging. Is there anything more than I can do?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,207
Members
448,554
Latest member
Gleisner2

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
Back
Top