Sum of numbers until zero value is hit

[bbelly]

Seems like a confusing description. Here's what I want to accomplish.

A B
-------
1 | 3 3
|
2 | 5 8
|
3 | 0 0
|
4 | 4 4
|
5 | 2 6
|
6 | 1 7

What I want to do is sum the series of column A (looking 'upward') until
a zero value is met. Then the sums start over.

In actual use, I am only looking at the past 7 or 14 days (two separate
columns)

It seems to me that this could be done by nesting IF functions.

Perhaps there is a better approach?

Thanks to all.

 

[JE McGimpsey]

One way:

B1: =A1
B2: =IF(A2=0,0,B1+A2)

Copy down as far as necessary

 

[PCLIVE]

Maybe something like this:

=SUM(INDIRECT("A1:A"&MATCH(0,A:A,0)))

HTH,
Paul

 

[Stan Brown]

Tue, 08 May 2007 03:16:55 GMT from bbelly <brian-
(e-mail address removed)>:

Seems like a confusing description. Here's what I want to accomplish.

A B
-------
1 | 3 3
2 | 5 8
3 | 0 0
4 | 4 4
5 | 2 6
6 | 1 7

What I want to do is sum the series of column A (looking 'upward') until
a zero value is met. Then the sums start over.

in B1: =A1
in B2: =if(A2=0,0,A2+B1)
then click and drag B2 down the rest of the column.

It seems to me that this could be done by nesting IF functions.

Perhaps there is a better approach?

Well, modesty prevents...

 

[bbelly]

Now that I see the solution(s) from JE & Stan, the dimmer on the light
bulb has turned way up.

I had been thinking in terms of PCLIVE's solution, but must adhere to
the KISS rule.

Thanks for the help

The second part of this is to only look at a maximum of seven entries.
(fourteen in another column). But stop the sum if a zero is hit(I guess
I didn't really make that point clear, since I was thinking in terms of
a nest to capture the last seven (14) values.

In other words, the sum of the last seven (14) entries until a zero
value is met.

Thanks again