Formula to add the above number (cell) until it finds "0"

[dave7352]

Hello all,

I'm try to do the below to make some very time consuming work a case of copying and pasting a formula but I don't even know if it's possible. Any help would be very much appreciated.

Basically I've got a column of numbers ranging from 0 to 10,000 and I want the cell next to the number to = the sum of all numbers above it until it hits a 0. Obviously I can do this manually but is there any formula that can do this so I can just copy & paste (drag) the formula down column C?

So for example (please see the attached)

Cell C5 would = A2 + A3 +A4 +A5 (but no cells above A1)
Cell C7 would = A6 +A7 (but no cells above A5)
Cell C10 would = A6 + A7 + A8 + A9 + A10 (but no cells above A5)
Cell C17 would = A16 + A17 (but no cells above A15)

What do you think? Is it possible?

Any help would be very much appreciated.

Dave

[Pepe Le Mokko]

Perhaps enter the following in B2

Please Login or Register  to view this content.

and pull down as needed

[dave7352]

Hi,

Thanks for your reply!

OK, I've tried this on my example and it works like a charm however when I try to implement it in my actual project I can't get it to work, I've edited the cell references to correspond with my file (A1 is now V6 and B1 is now W6) so I edited as follows....

=SUM($V$6:V7)-SUM($V$6:INDEX($V$6:$V$100,SUMPRODUCT(MAX(ROW($6:7)*($V$6:V7=0)))))

However it produces some very different outcomes to those expected. I've attached a portion (- some sensitive data) of the actual file.

What is it that I'm missing?

Thank you ever so much for your help with this it really is very much appreciated..

[dave7352]

The attachment I forgot to attach

[Pepe Le Mokko]

1.In column V you used =IF(G6="d","0",R6). The TRUE argument is text, while the formula needs a number. So, replace "0" with a plain 0

Please Login or Register  to view this content.

and pull down
2. You were on the right track with the formula except for the ROW() part. It should be row($1:2), because it is the "place" number in the range V6:V100 ( nothing to do with the row number of the sheet)

Please Login or Register  to view this content.

should do the trick provided you change your formula in col V