jonastiger
Member
Hi
I´m using the following formula to create a unique list based on multiple criteria:
{=IFERROR(INDEX(SERVICES[COD],MATCH(0,IF[SERVICES[OBD]=B$5,COUNTIF($B$6:B7,SERVICES[COD]),""),0)),"")} - This for B7 and cells below
=IFERROR(INDEX(SERVICES[CLIENT],MATCH(B7,SERVICES[COD],0)),"") - This for C7 and cells below
=SUMIFS(SERVICES[PROF],SERVICES[OBD],B$5,SERVICES[COD],B7) - This for D7 and cells bellow
SERVICES is a table with more than 10000 rows and it grows everyday
The distinct list has no more than 20 unique items.
I have 4 more structures with these formula starting at SERVICES[OBD]=F$5, SERVICES[OBD]=J$5, SERVICES[OBD]=N$5 and SERVICES[OBD]=R$5
This works, but everytime I change anything in this file, I have to wait for iterative calculation to finish to see the update, and it takes too damn long
Is there a way to change the array formula to make the calculation faster or convert this in a VBA option.
Thank you in advance
I´m using the following formula to create a unique list based on multiple criteria:
{=IFERROR(INDEX(SERVICES[COD],MATCH(0,IF[SERVICES[OBD]=B$5,COUNTIF($B$6:B7,SERVICES[COD]),""),0)),"")} - This for B7 and cells below
=IFERROR(INDEX(SERVICES[CLIENT],MATCH(B7,SERVICES[COD],0)),"") - This for C7 and cells below
=SUMIFS(SERVICES[PROF],SERVICES[OBD],B$5,SERVICES[COD],B7) - This for D7 and cells bellow
SERVICES is a table with more than 10000 rows and it grows everyday
The distinct list has no more than 20 unique items.
I have 4 more structures with these formula starting at SERVICES[OBD]=F$5, SERVICES[OBD]=J$5, SERVICES[OBD]=N$5 and SERVICES[OBD]=R$5
This works, but everytime I change anything in this file, I have to wait for iterative calculation to finish to see the update, and it takes too damn long
Is there a way to change the array formula to make the calculation faster or convert this in a VBA option.
Thank you in advance