Forum Discussion
Speeding up a slow-running formula on large Excel dataset
I have recently found a SUMPRODUCT formula that gives me exactly the calculations I'm looking for - the issue I'm having is performance. I'm aware that the SUMPRODUCT function is 'expensive', but it provides exactly what I'm looking for in this instance. My formula looks like this: =SUMPRODUCT(($E$2:$E$481864=$E2)*($G$2:$G$481864=2019)*($H$2:$H$481864=$H2)*($I$2:$I$481864=$I2)*$D$2:$D$481864)
As one can see, I have 3 separate column calculations covering ~500K rows plus another column that evaluates for a particular Year. I've changed the Calculations from 'Automatic' to 'Manual'. I've also enabled multi-threaded calculation and verified that all 12 available processors are being used. Is there anything else I can do to increase performance?
I currently have the DELL Inspiron 14 2-in-1 (Model: 7420), which has Windows 11 OS, 16 GB memory, 512GB SSD Graphics card with an Intel Core i7-1255U Processor.
4 Replies
- This was written for 'Legacy Excel' but still a good resource for what goes into calculation performance.
https://learn.microsoft.com/en-us/office/vba/excel/concepts/excel-performance/excel-improving-calculation-performance - I don't know if it would be much if any more efficient but you could try SUMIFS which should look like:
=SUMIFS($D$2:$D$481864, $E$2:$E$481864,$E2, $G$2:$G$481864,2019, $H$2:$H$481864,$H2, $I$2:$I$481864,$I2)- Of course - much neater than mine!
So, the best solution is probably to simplify the formula itself, or change the solution altogether.
I can't be certain this will be quicker, as I don't have your data or set-up, but I would be surprised if it doesn't help. Basically I created a helper column in column "J" containing the formula '=E2&G2&H2&I2', and then added a '=SUMIF(J2:J15,E2&"2019"&H2&I2,D2:D15)' formula to get the total (I only had test data in rows 2 - 15). On my simple test set this gives the same result as the SUMPRODUCT solution, unless I misunderstood your scenario. (One can then hide the helper column to keep it looking decent).
[A similar helper column option would be as follows '=(E2&G2&H2&I2=E$2&"2019"&H$2&I$2)*D2' - then you can just sum the column, and it won't look so messy if you don't like the idea of hiding it].
The above is assuming you are just looking for a single total to be displayed, if I am wrong and the SUMPRODUCT formula is on every line, then along with the aforementioned helper column, replace it with this version of SUMIF: '=SUMIF(J$2:J$15,E2&"2019"&H2&I2,D$2:D$15)'.
Hope this helps.
Mark
