formula to stop calculating when it comes across a particular valu

  • Thread starter Thread starter Roachy
  • Start date Start date
R

Roachy

I have spent hours trying to do this I now have less hair and whats left is
turning grey quickly, please help

Im trying to create a formula that sum,s up until it hits a particular value
and then stops so for example:
A B C
0 9 0
0 10 0
0 2 0
1 5 26
0 5 0
1 10 15
So in the example above I need the formula to sum up column B until it finds
a numerical value of 1 in in column A so the value 26 would show in column C
and the calculation would then start again from the next row.

Good luck understanding this and hope you help

Cheers
 
To simplify the formulas, use an extra column:

In C1 enter:
=B1
In C2 enter:
=IF(A1=1,B2,C1+B2) and copy down
in D1 enter:
=IF(A1=0,0,C1) and copy down

We now see:

0 9 9 0
0 10 19 0
0 2 21 0
1 5 26 26
0 5 5 0
1 10 15 15
 
Cheers for this Gary, but the formulas below will give the outcome of 26 but
on the second one gives the outcome of (26+ the 15) 41. Is there anyway that
it can just calculate upto the last numerical 1 meaning that it shows 26 &
15, also bearing in bind that this 1 may move up and down the list?

Im not sure if this is possible but it would be really useful

Thanks again
 
Using Gary's formulas, I get exactly the result he lists in his post - last
value is 15, not 41!
 
Back
Top