sum of cells until reach empty cell

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
phsdave said:
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
Click to expand...


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

=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, no
just Enter

and copy down

--
HT

Bob Phillip

(replace somewhere in email address with gmail if mailing direct

containes a

Previous Posts In This Thread:

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

In B2,
In B2, ente

=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, no
just Enter

and copy down

--
HT

Bob Phillip

(replace somewhere in email address with gmail if mailing direct

containes a

Re: sum of cells until reach empty cell
"phsdave" <[email protected]> skrev i en meddelelse

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

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

--
Best regard
Leo Heuse

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/tutorial...71-c05dd05ba1fd/handling-binary-and-text.aspx
 
David,

Is this array formula what you need?

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

HTH

Bob
 
Back
Top