Forum Discussion
SUMIFS formula until a combination of multiple criteria changes?
=SUMPRODUCT((MONTH($A$1:$A$20)=MONTH(A1))*(YEAR($A$1:$A$20)=YEAR(A1))*($B$1:$B$20=B1)*$C$1:$C$20)An alternative could be SUMPRODUCT. Does this formula return the expected result in the example?
- PatDoolsMar 28, 2023Brass Contributor
OliverScheurich- the formula you provided appears to be doing the trick - thank you! 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 you 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.
- SergeiBaklanMar 28, 2023Diamond Contributor
Please check recommendations here Excel performance - Tips for optimizing performance obstructions | Microsoft Learn
( sumproduct, sumifs, etc)