SUMIFS formula until a combination of multiple criteria changes?

[PatDools]

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 combination of 3 criteria until that changes (and so on from there)?

In my case, I want SUMIFS to sum while a combination of month, year and age category are identical, and then reset.  For example, SUMIFS while Month = 4, Year = 2019, and Age Category = 0-2 yrs, based on the values present in each row of my dataset. So, if Rows 1-20 have the aforementioned values, and then in Row 21, Month changes to 5, I want the SUMIFS to sum up the Month = 5, Year = 2019, and Age Category = 0-2 yrs combination.  I hope my example is clear - thank you!

[#2]

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

 

[#3]

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!

[#4]

@Quadruple_Pawn- 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.

[#5]

@PatDools 

Please check recommendations here Excel performance - Tips for optimizing performance obstructions | Microsoft Learn

( sumproduct, sumifs, etc)