sum the first column between the incidences of a 2 appearing

[demarc]

Question.xlsx

Hi all,

I have a problem that I cannot solve and would love some advice. In the attached file I have two columns of data. The first column of has a bunch on 0's 1's and 2's and what I am trying to do in the second column is sum the first column between the incidences of a 2 appearing. The problem I am having is coming up with a formula that has a variable range that says something like:

=IF(B7=2, SUM("all the 1's column B from this row up until you get to another 2 in column B"),"")

I have not posted here for a long time so hope the attachement works.

Thanks for any assistance you can provide.

Cheers,
Marcus

[Pepe Le Mokko]

Perhaps this in C4 and pulled down?

=IF(B4=2,SUM(B$3:$B3)-SUM(C$3:$C3)-2*COUNTIF($B$3:B3,2),"")

[demarc]

That works fine, thanks for the quick reply!

Cheers,
Marcus