SUM Range Between Zeros
 

Greetings Excel Gurus,

I've searched the knowlege base for a formula to solve my issue, but have
found nothing.

Here's an illustration (where "A" is the column and numbers 1-6 downward are
rows):

A
1 <header
2 5
3 0
4 15
5 20
6 <formula

The formula in A6 would sum cells A4 and A5 (all values 0 between the
formula and first cell upward that contains a zero (wouldn't include cell
A2).

Thanks for your help!

SUM Range Between Zeros
 

Try this:

=IF(COUNTIF(A2:A5,0),SUM(INDEX(A2:A5,MATCH(0,A2:A5 ,0)):A5),"")

Biff

"Exceller" wrote in message
...
Greetings Excel Gurus,

I've searched the knowlege base for a formula to solve my issue, but have
found nothing.

Here's an illustration (where "A" is the column and numbers 1-6 downward
are
rows):

A
1 <header
2 5
3 0
4 15
5 20
6 <formula

The formula in A6 would sum cells A4 and A5 (all values 0 between the
formula and first cell upward that contains a zero (wouldn't include cell
A2).

Thanks for your help!

SUM Range Between Zeros
 

Here you go....

NON-array formula
A6: =SUM(INDEX(A2:A5,MAX(INDEX((A2:A5=0)*ROW(A2:A5),0) )):A5)

or...the ARRAY FORMULA version
(committed with ctrl+shift+enter, instead of enter)
A6: =SUM(INDEX(A2:A5,MAX((A2:A5=0)*ROW(A2:A5))):A5)

OR....if there may be blank values.....
NON-array formula
A6: =SUM(INDEX(A2:A5,MAX(INDEX(ISNUMBER(A2:A5)*(A2:A5= 0)*ROW(A2:A5),0))):A5)

ARRAY FORMULA
A6: =SUM(INDEX(A2:A5,MAX(ISNUMBER(A2:A5)*(A2:A5=0)*ROW (A2:A5))):A5)

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Exceller" wrote:

Greetings Excel Gurus,

I've searched the knowlege base for a formula to solve my issue, but have
found nothing.

Here's an illustration (where "A" is the column and numbers 1-6 downward are
rows):

A
1 <header
2 5
3 0
4 15
5 20
6 <formula

The formula in A6 would sum cells A4 and A5 (all values 0 between the
formula and first cell upward that contains a zero (wouldn't include cell
A2).

Thanks for your help!

SUM Range Between Zeros
 

Thank you, Ron. It works, but if I mix the references and extend the formula
down the range, subsequent zeros entered make the references below it return
errors. For example, when I changed the references and dragged it down the
column to extended the range, and then added zeroes within the range
(overwriting the formula) all the formulas below it returned an error

Formula with changed references, dragged down to row 20
=SUM(INDEX($A$2:$A200,MAX(INDEX(($A$2:$A200=0)*ROW ($A$2:$A200),0))):$A200)

So, I need the formula to dynamically sum the values between the zeros
within the entire range (A2: A200); or really, I need it to sum the values
above it 0, until it reaches the first 0 above it.

Sorry if this is confusing! I'll be overwriting the formula in some cells in
range A2:A200 with zeros and need the formula to adjust for this (and count
all values above it 0, until it reaches the next zero, upward.

Thanks!

"Ron Coderre" wrote:

Here you go....

NON-array formula
A6: =SUM(INDEX(A2:A5,MAX(INDEX((A2:A5=0)*ROW(A2:A5),0) )):A5)

or...the ARRAY FORMULA version
(committed with ctrl+shift+enter, instead of enter)
A6: =SUM(INDEX(A2:A5,MAX((A2:A5=0)*ROW(A2:A5))):A5)

OR....if there may be blank values.....
NON-array formula
A6: =SUM(INDEX(A2:A5,MAX(INDEX(ISNUMBER(A2:A5)*(A2:A5= 0)*ROW(A2:A5),0))):A5)

ARRAY FORMULA
A6: =SUM(INDEX(A2:A5,MAX(ISNUMBER(A2:A5)*(A2:A5=0)*ROW (A2:A5))):A5)

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Exceller" wrote:

Greetings Excel Gurus,

I've searched the knowlege base for a formula to solve my issue, but have
found nothing.

Here's an illustration (where "A" is the column and numbers 1-6 downward are
rows):

A
1 <header
2 5
3 0
4 15
5 20
6 <formula

The formula in A6 would sum cells A4 and A5 (all values 0 between the
formula and first cell upward that contains a zero (wouldn't include cell
A2).

Thanks for your help!

SUM Range Between Zeros
 

Exceller wrote...
Thank you, Ron. It works, but if I mix the references and extend the formula
down the range, subsequent zeros entered make the references below it return
errors. For example, when I changed the references and dragged it down the
column to extended the range, and then added zeroes within the range
(overwriting the formula) all the formulas below it returned an error

Formula with changed references, dragged down to row 20
=SUM(INDEX($A$2:$A200,
MAX(INDEX(($A$2:$A200=0)*ROW($A$2:$A200),0))):$A2 00)
....

Ron's formula has a bug in it. First off,

MAX(ISNUMBER(A2:A5)*(A2:A5=0)*ROW(A2:A5))

returns 2, 3, 4 or 5, but the row indices for A2:A5 are 1, 2, 3 and 4.
An off-by-one bug. If the last zero is in the last row, the MAX term
returns one more than the number of rows in the range, and that leads
to a #REF! error.

So, I need the formula to dynamically sum the values between the zeros
within the entire range (A2: A200); or really, I need it to sum the values
above it 0, until it reaches the first 0 above it.
....

If the formula should return zero if the value immediately above it
were zero, try

=SUMPRODUCT(--(ROW(rng)LOOKUP(2,1/(rng=0),ROW(rng))),rng)

and if the range could contain blank cells, try

=SUMPRODUCT(--(ROW(rng)LOOKUP(2,1/ISNUMBER(rng)/
(rng=0),ROW(rng))),rng)

SUM Range Between Zeros
 

If you want the formula to be able to distinguish input values from formulas,
then a User Defined Function may be in your future. Otherwise....maybe this?

A6: =SUM(INDEX($A$2:A5,MAX(INDEX(($A$2:A5=0)*ROW($A$2: A5)-ROW($A$1),0))):A5)

Copy that formula down

Note: the first formula after the inputs will return the correct value, the
remaining formulas will feed upon those above.

Is that something you can work with?

***********
Regards,
Ron

XL2002, WinXP


"Exceller" wrote:

Thank you, Ron. It works, but if I mix the references and extend the formula
down the range, subsequent zeros entered make the references below it return
errors. For example, when I changed the references and dragged it down the
column to extended the range, and then added zeroes within the range
(overwriting the formula) all the formulas below it returned an error

Formula with changed references, dragged down to row 20
=SUM(INDEX($A$2:$A200,MAX(INDEX(($A$2:$A200=0)*ROW ($A$2:$A200),0))):$A200)

So, I need the formula to dynamically sum the values between the zeros
within the entire range (A2: A200); or really, I need it to sum the values
above it 0, until it reaches the first 0 above it.

Sorry if this is confusing! I'll be overwriting the formula in some cells in
range A2:A200 with zeros and need the formula to adjust for this (and count
all values above it 0, until it reaches the next zero, upward.

Thanks!

"Ron Coderre" wrote:

Here you go....

NON-array formula
A6: =SUM(INDEX(A2:A5,MAX(INDEX((A2:A5=0)*ROW(A2:A5),0) )):A5)

or...the ARRAY FORMULA version
(committed with ctrl+shift+enter, instead of enter)
A6: =SUM(INDEX(A2:A5,MAX((A2:A5=0)*ROW(A2:A5))):A5)

OR....if there may be blank values.....
NON-array formula
A6: =SUM(INDEX(A2:A5,MAX(INDEX(ISNUMBER(A2:A5)*(A2:A5= 0)*ROW(A2:A5),0))):A5)

ARRAY FORMULA
A6: =SUM(INDEX(A2:A5,MAX(ISNUMBER(A2:A5)*(A2:A5=0)*ROW (A2:A5))):A5)

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Exceller" wrote:

Greetings Excel Gurus,

I've searched the knowlege base for a formula to solve my issue, but have
found nothing.

Here's an illustration (where "A" is the column and numbers 1-6 downward are
rows):

A
1 <header
2 5
3 0
4 15
5 20
6 <formula

The formula in A6 would sum cells A4 and A5 (all values 0 between the
formula and first cell upward that contains a zero (wouldn't include cell
A2).

Thanks for your help!

SUM Range Between Zeros
 

Harlan
Ron
Your formulas work wonderfully. Thanks so much for your expertise and
assistance!
T--I couldn't get your formula to work in this scenario, but I've modified
it for another spreadsheet and it works great there--thanks!
Many, many thanks to all.
Regards,
Exceller

"Ron Coderre" wrote:

If you want the formula to be able to distinguish input values from formulas,
then a User Defined Function may be in your future. Otherwise....maybe this?

A6: =SUM(INDEX($A$2:A5,MAX(INDEX(($A$2:A5=0)*ROW($A$2: A5)-ROW($A$1),0))):A5)

Copy that formula down

Note: the first formula after the inputs will return the correct value, the
remaining formulas will feed upon those above.

Is that something you can work with?

***********
Regards,
Ron

XL2002, WinXP


"Exceller" wrote:

Thank you, Ron. It works, but if I mix the references and extend the formula
down the range, subsequent zeros entered make the references below it return
errors. For example, when I changed the references and dragged it down the
column to extended the range, and then added zeroes within the range
(overwriting the formula) all the formulas below it returned an error

Formula with changed references, dragged down to row 20
=SUM(INDEX($A$2:$A200,MAX(INDEX(($A$2:$A200=0)*ROW ($A$2:$A200),0))):$A200)

So, I need the formula to dynamically sum the values between the zeros
within the entire range (A2: A200); or really, I need it to sum the values
above it 0, until it reaches the first 0 above it.

Sorry if this is confusing! I'll be overwriting the formula in some cells in
range A2:A200 with zeros and need the formula to adjust for this (and count
all values above it 0, until it reaches the next zero, upward.

Thanks!

"Ron Coderre" wrote:

Here you go....

NON-array formula
A6: =SUM(INDEX(A2:A5,MAX(INDEX((A2:A5=0)*ROW(A2:A5),0) )):A5)

or...the ARRAY FORMULA version
(committed with ctrl+shift+enter, instead of enter)
A6: =SUM(INDEX(A2:A5,MAX((A2:A5=0)*ROW(A2:A5))):A5)

OR....if there may be blank values.....
NON-array formula
A6: =SUM(INDEX(A2:A5,MAX(INDEX(ISNUMBER(A2:A5)*(A2:A5= 0)*ROW(A2:A5),0))):A5)

ARRAY FORMULA
A6: =SUM(INDEX(A2:A5,MAX(ISNUMBER(A2:A5)*(A2:A5=0)*ROW (A2:A5))):A5)

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Exceller" wrote:

Greetings Excel Gurus,

I've searched the knowlege base for a formula to solve my issue, but have
found nothing.

Here's an illustration (where "A" is the column and numbers 1-6 downward are
rows):

A
1 <header
2 5
3 0
4 15
5 20
6 <formula

The formula in A6 would sum cells A4 and A5 (all values 0 between the
formula and first cell upward that contains a zero (wouldn't include cell
A2).

Thanks for your help!

SUM Range Between Zeros
 

Thanks for the feedback, Exceller.


***********
Regards,
Ron

XL2002, WinXP


"Exceller" wrote:

Harlan
Ron
Your formulas work wonderfully. Thanks so much for your expertise and
assistance!
T--I couldn't get your formula to work in this scenario, but I've modified
it for another spreadsheet and it works great there--thanks!
Many, many thanks to all.
Regards,
Exceller

"Ron Coderre" wrote:

If you want the formula to be able to distinguish input values from formulas,
then a User Defined Function may be in your future. Otherwise....maybe this?

A6: =SUM(INDEX($A$2:A5,MAX(INDEX(($A$2:A5=0)*ROW($A$2: A5)-ROW($A$1),0))):A5)

Copy that formula down

Note: the first formula after the inputs will return the correct value, the
remaining formulas will feed upon those above.

Is that something you can work with?

***********
Regards,
Ron

XL2002, WinXP


"Exceller" wrote:

Thank you, Ron. It works, but if I mix the references and extend the formula
down the range, subsequent zeros entered make the references below it return
errors. For example, when I changed the references and dragged it down the
column to extended the range, and then added zeroes within the range
(overwriting the formula) all the formulas below it returned an error

Formula with changed references, dragged down to row 20
=SUM(INDEX($A$2:$A200,MAX(INDEX(($A$2:$A200=0)*ROW ($A$2:$A200),0))):$A200)

So, I need the formula to dynamically sum the values between the zeros
within the entire range (A2: A200); or really, I need it to sum the values
above it 0, until it reaches the first 0 above it.

Sorry if this is confusing! I'll be overwriting the formula in some cells in
range A2:A200 with zeros and need the formula to adjust for this (and count
all values above it 0, until it reaches the next zero, upward.

Thanks!

"Ron Coderre" wrote:

Here you go....

NON-array formula
A6: =SUM(INDEX(A2:A5,MAX(INDEX((A2:A5=0)*ROW(A2:A5),0) )):A5)

or...the ARRAY FORMULA version
(committed with ctrl+shift+enter, instead of enter)
A6: =SUM(INDEX(A2:A5,MAX((A2:A5=0)*ROW(A2:A5))):A5)

OR....if there may be blank values.....
NON-array formula
A6: =SUM(INDEX(A2:A5,MAX(INDEX(ISNUMBER(A2:A5)*(A2:A5= 0)*ROW(A2:A5),0))):A5)

ARRAY FORMULA
A6: =SUM(INDEX(A2:A5,MAX(ISNUMBER(A2:A5)*(A2:A5=0)*ROW (A2:A5))):A5)

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Exceller" wrote:

Greetings Excel Gurus,

I've searched the knowlege base for a formula to solve my issue, but have
found nothing.

Here's an illustration (where "A" is the column and numbers 1-6 downward are
rows):

A
1 <header
2 5
3 0
4 15
5 20
6 <formula

The formula in A6 would sum cells A4 and A5 (all values 0 between the
formula and first cell upward that contains a zero (wouldn't include cell
A2).

Thanks for your help!

SUM Range Between Zeros
 

You're welcome. This is a great resource and service, and the MS MVP program
and its members deserve a lot of credit and many thanks.
Thanks again.

"Ron Coderre" wrote:

Thanks for the feedback, Exceller.


***********
Regards,
Ron

XL2002, WinXP


"Exceller" wrote:

Harlan
Ron
Your formulas work wonderfully. Thanks so much for your expertise and
assistance!
T--I couldn't get your formula to work in this scenario, but I've modified
it for another spreadsheet and it works great there--thanks!
Many, many thanks to all.
Regards,
Exceller

"Ron Coderre" wrote:

If you want the formula to be able to distinguish input values from formulas,
then a User Defined Function may be in your future. Otherwise....maybe this?

A6: =SUM(INDEX($A$2:A5,MAX(INDEX(($A$2:A5=0)*ROW($A$2: A5)-ROW($A$1),0))):A5)

Copy that formula down

Note: the first formula after the inputs will return the correct value, the
remaining formulas will feed upon those above.

Is that something you can work with?

***********
Regards,
Ron

XL2002, WinXP


"Exceller" wrote:

Thank you, Ron. It works, but if I mix the references and extend the formula
down the range, subsequent zeros entered make the references below it return
errors. For example, when I changed the references and dragged it down the
column to extended the range, and then added zeroes within the range
(overwriting the formula) all the formulas below it returned an error

Formula with changed references, dragged down to row 20
=SUM(INDEX($A$2:$A200,MAX(INDEX(($A$2:$A200=0)*ROW ($A$2:$A200),0))):$A200)

So, I need the formula to dynamically sum the values between the zeros
within the entire range (A2: A200); or really, I need it to sum the values
above it 0, until it reaches the first 0 above it.

Sorry if this is confusing! I'll be overwriting the formula in some cells in
range A2:A200 with zeros and need the formula to adjust for this (and count
all values above it 0, until it reaches the next zero, upward.

Thanks!

"Ron Coderre" wrote:

Here you go....

NON-array formula
A6: =SUM(INDEX(A2:A5,MAX(INDEX((A2:A5=0)*ROW(A2:A5),0) )):A5)

or...the ARRAY FORMULA version
(committed with ctrl+shift+enter, instead of enter)
A6: =SUM(INDEX(A2:A5,MAX((A2:A5=0)*ROW(A2:A5))):A5)

OR....if there may be blank values.....
NON-array formula
A6: =SUM(INDEX(A2:A5,MAX(INDEX(ISNUMBER(A2:A5)*(A2:A5= 0)*ROW(A2:A5),0))):A5)

ARRAY FORMULA
A6: =SUM(INDEX(A2:A5,MAX(ISNUMBER(A2:A5)*(A2:A5=0)*ROW (A2:A5))):A5)

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Exceller" wrote:

Greetings Excel Gurus,

I've searched the knowlege base for a formula to solve my issue, but have
found nothing.

Here's an illustration (where "A" is the column and numbers 1-6 downward are
rows):

A
1 <header
2 5
3 0
4 15
5 20
6 <formula

The formula in A6 would sum cells A4 and A5 (all values 0 between the
formula and first cell upward that contains a zero (wouldn't include cell
A2).

Thanks for your help!