Forum Discussion

PatDools's avatar
PatDools
Brass Contributor
Mar 27, 2023

SUMIFS formula until a combination of multiple criteria changes?

Hello, I have used the SUMIFS function in many Excel formulas.  How do I create a formula that evaluates when the combination of 3 criteria changes and then performs the SUMIFS on the next combinati...
excel
Formulas and Functions
OliverScheurich's avatar
OliverScheurich
Gold Contributor
Mar 27, 2023

PatDools 

=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?

 

PatDools's avatar
PatDools
Brass Contributor
Mar 27, 2023
Hello - it appears that it might do what I'm looking for, as long as ANY change in either Month or Year or Age Category results in a rest of the sum counter. I'll test it our on my own dataset and will provide an update. Thank you!
  • PatDools's avatar
    PatDools
    Brass Contributor
    Mar 28, 2023

    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.

Resources