sum of cells until reach empty cell
 

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

sum of cells until reach empty cell
 

In B2, enter

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

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

and copy down.

--
HTH

Bob Phillips

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

"phsdave" wrote in message
...
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

sum of cells until reach empty cell
 

"phsdave" skrev i en meddelelse
...
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),"")

--
Best regards
Leo Heuser

Followup to newsgroup only please.

slight variation
 

Bob, Im trying to use the formula you have listed to total data in column A based on markers in column B. (column A is data, column B is either 1 or 0) Id like it to start totalling column A from where there is a 1 in Column B up till it hits a 0.

There would be several sets to be summed in the column so its just from 1 to the 0 immediately above it then it restarts

David



Bob Phillips wrote:

In B2,
28-Oct-06

In B2, enter

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

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

and copy down.

--
HTH

Bob Phillips

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

"phsdave" wrote in message
...
containes a

Previous Posts In This Thread:

On Saturday, October 28, 2006 5:17 AM
phsdav wrote:

sum of cells until reach empty cell
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

On Saturday, October 28, 2006 8:19 AM
Bob Phillips wrote:

In B2,
In B2, enter

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

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

and copy down.

--
HTH

Bob Phillips

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

"phsdave" wrote in message
...
containes a

On Saturday, October 28, 2006 9:13 AM
Leo Heuser wrote:

sum of cells until reach empty cell
"phsdave" skrev i en meddelelse
...


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),"")

--
Best regards
Leo Heuser

Followup to newsgroup only please.


Submitted via EggHeadCafe - Software Developer Portal of Choice
HANDLING BINARY AND TEXT DATA IN XML OVER THE WIRE
http://www.eggheadcafe.com/tutorials...-and-text.aspx

slight variation
 

David,

Is this array formula what you need?

=IF(B2=1,SUM(A2:INDEX(A:A,MAX(2,MIN(IF($B$2:B2=0,R OW($B$2:B2)+1))))),"")

HTH

Bob

<DAVID BLAKE wrote in message ...
Bob, Im trying to use the formula you have listed to total data in column
A based on markers in column B. (column A is data, column B is either 1 or
0) Id like it to start totalling column A from where there is a 1 in
Column B up till it hits a 0.

There would be several sets to be summed in the column so its just from 1
to the 0 immediately above it then it restarts

David



Bob Phillips wrote:

In B2,
28-Oct-06

In B2, enter

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

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

and copy down.

--
HTH

Bob Phillips

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

"phsdave" wrote in message
...
containes a

Previous Posts In This Thread:

On Saturday, October 28, 2006 5:17 AM
phsdav wrote:

sum of cells until reach empty cell
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

On Saturday, October 28, 2006 8:19 AM
Bob Phillips wrote:

In B2,
In B2, enter

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

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

and copy down.

--
HTH

Bob Phillips

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

"phsdave" wrote in message
...
containes a

On Saturday, October 28, 2006 9:13 AM
Leo Heuser wrote:

sum of cells until reach empty cell
"phsdave" skrev i en meddelelse
...


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),"")

--
Best regards
Leo Heuser

Followup to newsgroup only please.


Submitted via EggHeadCafe - Software Developer Portal of Choice
HANDLING BINARY AND TEXT DATA IN XML OVER THE WIRE
http://www.eggheadcafe.com/tutorials...-and-text.aspx