Search code examples
google-sheetscountsumtransposecumulative-sum

Google Sheets - Running Total with reset


Ok I have the following data from my brokerage:

Amount Description
-0.5 EXCHANGE FEE
-0.09 CLEARING FEE
-0.25 COMMISSIONS
20 TRADE PAIRED
-0.5 EXCHANGE FEE
-0.09 CLEARING FEE
-0.25 COMMISSIONS
10 TRADE PAIRED

What I would like to do is having a running total that resets after it encounters "TRADE PAIRED" in the Description column. So it would look like this:

Amount Description Running Total
-0.5 EXCHANGE FEE -0.5
-0.09 CLEARING FEE -0.59
-0.25 COMMISSIONS -0.84
20 TRADE PAIRED 19.16
-0.5 EXCHANGE FEE -0.5
-0.09 CLEARING FEE -0.59
-0.25 COMMISSIONS -0.84
10 TRADE PAIRED 9.16

Anyone have an ARRAYFORMULA that would be able to do this? I've been to do a continuous running total but it's not resetting after "TRADE PAIRED"

=ArrayFormula(if((G5:G<>"TRADE PAIRED"),MMULT(IF(ROW(D5:D)>=TRANSPOSE(ROW(D5:D))=TRUE,1,0),if(G5:G<>"TRADE PAIRED",n(D5:D),0)),MMULT(IF(ROW(D5:D)>=TRANSPOSE(ROW(D5:D))=TRUE,1,0),if(G5:G<>"TRADE PAIRED",n(D5:D),0))+D5:D)

This is not a duplicate of existing: ArrayFormula of Resetting Running Total in Google Sheets

As that one is grouping based off a common value. Mine is resetting based on a specific value. Additionally, the formula provided in the link crashes Google Sheets


Solution

  • try:

    =INDEX(IF(A:A="",,MMULT(--TRANSPOSE(IF((TRANSPOSE(ROW(A:A))>=ROW(A:A))*(
     {0; ARRAY_CONSTRAIN(COUNTIFS(B:B, "TRADE PAIRED", 
     ROW(B:B), "<="&ROW(B:B)), ROWS(A:A)-1, 1)}=TRANSPOSE(
     {0; ARRAY_CONSTRAIN(COUNTIFS(B:B, "TRADE PAIRED", 
     ROW(B:B), "<="&ROW(B:B)), ROWS(A:A)-1, 1)})),A:A, 0)), ROW(A:A)^0)))
    

    enter image description here