Forum Discussion

PatDools's avatar
PatDools
Brass Contributor
Mar 28, 2023

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 ...
excel
Formulas and Functions
Mark_Walters's avatar
Mark_Walters
Copper Contributor
Mar 28, 2023

PatDools 

 

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

Resources