Dear Experts,

I am working on a data set that has about 40k+ rows and 20 columns. This data will continue to grow till the end of the year.

Naturally i used a sumifs to aggregate my data into the layout (see the summarized sheet) that i wanted to report on. However, i noticed that the sumifs formula was taking huge amount of time to calculate and had to resort first to turning off calculation mode and then click on the calculate button to update the sheet. i have done all what i think i know to make it faster but its not working out for me.

Hence i am wondering if somebody can help:

Take a look at my sumifs formula to see if i goofed it
Help write a application.workfunction.sumifs version to see if this can speed up the process or
simply have a better way i can use in aggregating this data so that i can continue with my project. (using pivot will add lots of other steps to the process and i am trying to avoid that.


Please find attached a desensitized version of my data. Sumifs_ish.xlsx

Cross Link: http://www.ozgrid.com/forum/showthread.php?t=197213

Many thanks for your time,

Kay