sum of cells until reach empty cell

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

phsdave - 28 Oct 2006 10:17 GMT

I would like to get the sum of 7 cells which restarts when a cell containes a
zero.
I want to have a result in an ajoining cell of the sum to a maximum of 7
cells in the ajoining column

Reply to this Message

Bob Phillips - 28 Oct 2006 13:19 GMT

In B2, enter

=IF(A2=0,SUM(INDEX(A:A,MAX(IF($A$1:A1=0,ROW($A$1:A1)))+1):A1),"")

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

and copy down.

Signature

HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

> I would like to get the sum of 7 cells which restarts when a cell containes a
> zero.
> I want to have a result in an ajoining cell of the sum to a maximum of 7
> cells in the ajoining column

Reply to this Message

Leo Heuser - 28 Oct 2006 14:13 GMT

>I would like to get the sum of 7 cells which restarts when a cell containes
>a
> zero.
> I want to have a result in an ajoining cell of the sum to a maximum of 7
> cells in the ajoining column

Assuming your data in A2 and down and further assuming
B1 is present and empty (or containing text):

In B2:
=IF(A2=0,SUM($A$2:A2)-SUM($B$1:B1),"")

Signature

Best regards
Leo Heuser

Followup to newsgroup only please.

Reply to this Message