CONDITIONAL RUNNING SUM

Question

I am trying to use the following formula to sum values in column A when the value in Column A is zero however the formula is returning last value instead of summing all value i.e. in cell B4 the value should be 500 instead of 300. Please guide how solve the problem ?

=IF(A2=0,SUM(XLOOKUP(TRUE,B$1:B1<>"",A$1:A1,,,-1):A2),0)

Sample data

A	B
200	-
300	-
-	300
100	-
600	-
-	600
	-
300	-
600	-
-	600

Answer 1

Hi FazliWahid,

See if the below formula works for you. Apply this formula in cell B2 and drag it down.

=IFERROR(IF(A2 ="", SUM((INDEX(A$1:A$15, LOOKUP(2, 1/(SUBSTITUTE(A$1:A1, "Value", "") = ""), ROW(A1:A$2))) : INDEX(A1:A$15, MATCH(TRUE, INDEX(A1:A$15 = "", ), 0) -1))), ""), "")

Change the word Value in formula with the appropriate word that you have specified for head of column A.

Note: In order for the formula to work, you will need to replace the zero value cells with empty cells in column A.

Hope this helps.

Answer 2

Hi,

In cell D2, enter this formula and drag down

=IF(C2=0,SUM(INDEX(A$1:A1,XLOOKUP(TRUE,C$1:C1=0,ROW(C$1:C1),2,,-1),1):A2),0)

Hope this helps.

Answer 3

You are welcome. In cell B2, enter this formula and drag down

=IF(A2="","",IF(A2=0,SUM(XLOOKUP(1,((A$1:A1=0)+(ISTEXT(A$1:A1))),A$1:A1,,,-1):A2),""))

Hope this helps.

Answer 4

Hi,

In cell B2, enter this formula

=IF(A2="","",IF(A2=0,SUM(XLOOKUP(TRUE,A$1:A1=0,A$1:A1,,,-1):A2),""))

Hope this helps.