Calculating speed issue

tola_ray

New Member
Joined
Feb 27, 2019
Messages
1
Dear All, I have create array formula to capture sum up individual record which is meet the criteria as : =SUM(IF(FREQUENCY(IF((Risky_Type_F="High Risk")*(Date_Education_F>=StartDate)*(Date_Education_F<=EndDate)*(Type_KP_F="EW")*(Match_Edu_E_F="Yes"),IF(UUIC_F<>"",MATCH("~"&UUIC_F,UUIC_F&"",0))),ROW(UUIC_F)-ROW(Education!$L$1)+1),1))

There are around 50,000 record in table and while running this formula it takes a lot of time. anyone have other way to make it run faster?
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,350
hello

Suggest you paste some sample data & explain in words the result you're after.

Excel has tools - like pivot tables - that are suited to efficiently working with well structured data (that is, set up in tables).

regards, Fazza
 

Watch MrExcel Video

Forum statistics

Threads
1,099,363
Messages
5,468,186
Members
406,570
Latest member
Ktvu2006

This Week's Hot Topics

Top