I'm trying to calculate the cumulative total in a column. It needs to sum or subtract if the value of (A) cell is set to buy or sell. here is an example:
| A (task) | B (qtty) | C (total) | calculation explanation |
|---|---|---|---|
| buy | 10 | 10 | sum 10 |
| buy | 10 | 20 | sum 10 |
| sell | 5 | 15 | subtract 5 |
| buy | 20 | 35 | sum 20 |
| sell | 10 | 25 | subtract 10 |
I´m using the folowing formula: ={"Total", ARRAYFORMULA(IF(LEN(A2:A),IF(A2:A="buy",SUMIF(ROW(B2:B),"<="&ROW(B2:B),B2:B), "NEED CODE FOR SELL" ),))}
Is there another way to do the calc?
I don't want to use negative values to subtract, because the values are used in other formulas.
Thanks in advance.
CodePudding user response：
try this:
=SCAN(0,A2:A,LAMBDA(ac,cv,if(cv="",,ac ifs(cv="buy",OFFSET(cv,,1),cv="sell",-OFFSET(cv,,1)))))
CodePudding user response：
You can use SCAN:
=ArrayFormula(IFNA(SCAN(,A2:A&B2:B,LAMBDA(tot,cur,tot REGEXEXTRACT(cur,"\d ")*IF(REGEXMATCH(cur,"(?i)buy"),1,-1)))))