RUNNING TOTAL with ARRAYFORMULA that can SUM and SUBTRACT if cell values changes

Question

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.

Answer 1

try this:

=SCAN(0,A2:A,LAMBDA(ac,cv,if(cv="",,ac + ifs(cv="buy",OFFSET(cv,,1),cv="sell",-OFFSET(cv,,1)))))