Mar 28 2023 09:51 AM
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.
Mar 28 2023 10:37 AM - edited Mar 28 2023 11:33 AM
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
Mar 28 2023 10:38 AM
Mar 28 2023 12:36 PM
Mar 28 2023 01:05 PM