Calculating a running sum with reset in Excel-CodePudding

I have a need to calculate a running sum of column D's count data in column E. However, I only want to calculate the running sum for the appropriate categories in columns B and C. In other words, there are four combinations of categories and I need a running sum for each. The easiest way is to do what I currently have in column F (cell F3=SUM($D$2:D3)) and drag it down through F11 and manually restart it at F12. I can't do this in my full dataset though because there are about 20k rows of data. So, I'm trying to make column E dynamically calculate what's in column F. I started with =SUMIFS() and can return the final sum for each combination of the two categories, but it's not a running sum, created dynamically, that resets with the new day count in column A.

Any suggestions would be appreciated. TIA

CodePudding user response：

It's a bit hacky, but you can add a few columns:

consecutive numbers from 2 to end of your data (say this is in Column G).
references to every 10th cell (e.g. G2, G12) for every cell in that cluster. (say this is in Column H)
you can fill in the first few rows for these two columns and then drag it down.
a reference to the count column ('D') in a single cell (say I1).

You can then use CONCATENATE and INDIRECT inside SUM:

Cumulative Count: SUM(INDIRECT(CONCATENATE($I$2,H2)):D2) and drag this down.

CodePudding user response：

If I understand the problem correctly, the solution is actually very simple.
This formula goes to E2 (and then copy down):

=IF(B1&C1<>B2&C2,D2,SUM(E1,D2))

In each case (including the first) of a change of either Cat A or Cat B, it takes the count value at that row (i.e. the new starting balance). Thereafter, it does a running balance addition (balance from row above count at this row), until the next change of Cat A or Cat B is encountered.

CodePudding user response：

all you need is a minor adjustment to the cumulative count:

=Sumifs($D$2:D2,$B$2:B2,B2,$C$2:C2,C2)

Put in Current Count and it is done. Drag/drop OR Autocomplete down as needed

Now it takes into account only above/previous than the current...