Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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) |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
LINKEDRANGE function - a complement to the PULL function (for getting values from a closed workbook) | Excel Worksheet Functions | |||
Wrap Text Across Columns & Rows | Excel Discussion (Misc queries) | |||
Duplicate Range Names by worksheet | Excel Discussion (Misc queries) | |||
Help with using range names in sum function | Excel Worksheet Functions | |||
Array to named range conversion... | Excel Discussion (Misc queries) |