Running Total with conditions
Can someone help? I want to keep a running total of one column but if it
encounters a zero in the column then I want the zero to reset the total to zero and continue from that point. Help is appreciated. -- neta |
This works for me when my list of values begin in Cell A1.
Put this formula in Cell B1 and copy down: =SUMPRODUCT(SUM(INDIRECT("$A$"&MAX(--($A$1:A1=0)*ROW($A$1:A1),1)&":"&CELL("address",A1) ))) Does that help? -- Regards, Ron "Midnight404" wrote: Can someone help? I want to keep a running total of one column but if it encounters a zero in the column then I want the zero to reset the total to zero and continue from that point. Help is appreciated. -- neta |
Running Total with conditions
Ron,
Nevertheless your last proposal does work when zero in but doesn't whenever non zero value is enetered (i.e cell below former zero doesn't add up as a running sum instead it does eqaul with former zero cell and all others on the right doesn't run total So if you want you canm post on forum where you'll find the update on the subject: Daniel, Some time ago you posted a solution for below quest. I found it very useful, well done ! Actually I needed to make it work on a transposed situation, i.e. rows to be add up become columns. It doesn't work when it encounters a zero (no reset happens), any hint ?? "Ron Coderre" wrote: Second: For values or zeros in Row_1 (blanks count as zeros in this case) B1: =SUMPRODUCT(SUM(INDIRECT(ADDRESS(1,MAX(--($A$1:A1=0)*COLUMN($A$1:A1),1))&":"&ADDRESS(1,COLU MN(A1))))) Copy that formula across to the right. It will calculate cumulative totals until it reads a zero in Row_1. At that point the cumulative total resets, beginning with the cell containing the zero. "Ron Coderre" wrote: This works for me when my list of values begin in Cell A1. Put this formula in Cell B1 and copy down: =SUMPRODUCT(SUM(INDIRECT("$A$"&MAX(--($A$1:A1=0)*ROW($A$1:A1),1)&":"&CELL("address",A1) ))) Does that help? -- Regards, Ron "Midnight404" wrote: Can someone help? I want to keep a running total of one column but if it encounters a zero in the column then I want the zero to reset the total to zero and continue from that point. Help is appreciated. -- neta |
Running Total with conditions
Daniel
My apologies....I must have sent you the wrong version of the formula! Try this: For values, zeros, or blanks in Row_1, use this ARRAY FORMULA* A2: =SUMPRODUCT(SUM(INDIRECT(ADDRESS(1,MAX(--($A$1:A1=0)*COLUMN($A$1:A1),1))):A1)) *Note: For array formulas, hold down [Ctrl] and [Shift] when you press [Enter], instead of just pressing [Enter]. Copy that formula into B2 and across. That should do it...Does it? *********** Regards, Ron XL2002, WinXP "daniel" wrote: Ron, Nevertheless your last proposal does work when zero in but doesn't whenever non zero value is enetered (i.e cell below former zero doesn't add up as a running sum instead it does eqaul with former zero cell and all others on the right doesn't run total So if you want you canm post on forum where you'll find the update on the subject: Daniel, Some time ago you posted a solution for below quest. I found it very useful, well done ! Actually I needed to make it work on a transposed situation, i.e. rows to be add up become columns. It doesn't work when it encounters a zero (no reset happens), any hint ?? "Ron Coderre" wrote: Second: For values or zeros in Row_1 (blanks count as zeros in this case) B1: =SUMPRODUCT(SUM(INDIRECT(ADDRESS(1,MAX(--($A$1:A1=0)*COLUMN($A$1:A1),1))&":"&ADDRESS(1,COLU MN(A1))))) Copy that formula across to the right. It will calculate cumulative totals until it reads a zero in Row_1. At that point the cumulative total resets, beginning with the cell containing the zero. "Ron Coderre" wrote: This works for me when my list of values begin in Cell A1. Put this formula in Cell B1 and copy down: =SUMPRODUCT(SUM(INDIRECT("$A$"&MAX(--($A$1:A1=0)*ROW($A$1:A1),1)&":"&CELL("address",A1) ))) Does that help? -- Regards, Ron "Midnight404" wrote: Can someone help? I want to keep a running total of one column but if it encounters a zero in the column then I want the zero to reset the total to zero and continue from that point. Help is appreciated. -- neta |
Running Total with conditions
Hi Ron,
Thanks for your effort, it works perfectly. I wish I had done it myself, I have no clue how it works, but sometime you have to believe in magic. All the best, Daniel. "Ron Coderre" wrote: Daniel My apologies....I must have sent you the wrong version of the formula! Try this: For values, zeros, or blanks in Row_1, use this ARRAY FORMULA* A2: =SUMPRODUCT(SUM(INDIRECT(ADDRESS(1,MAX(--($A$1:A1=0)*COLUMN($A$1:A1),1))):A1)) *Note: For array formulas, hold down [Ctrl] and [Shift] when you press [Enter], instead of just pressing [Enter]. Copy that formula into B2 and across. That should do it...Does it? *********** Regards, Ron XL2002, WinXP "daniel" wrote: Ron, Nevertheless your last proposal does work when zero in but doesn't whenever non zero value is enetered (i.e cell below former zero doesn't add up as a running sum instead it does eqaul with former zero cell and all others on the right doesn't run total So if you want you canm post on forum where you'll find the update on the subject: Daniel, Some time ago you posted a solution for below quest. I found it very useful, well done ! Actually I needed to make it work on a transposed situation, i.e. rows to be add up become columns. It doesn't work when it encounters a zero (no reset happens), any hint ?? "Ron Coderre" wrote: Second: For values or zeros in Row_1 (blanks count as zeros in this case) B1: =SUMPRODUCT(SUM(INDIRECT(ADDRESS(1,MAX(--($A$1:A1=0)*COLUMN($A$1:A1),1))&":"&ADDRESS(1,COLU MN(A1))))) Copy that formula across to the right. It will calculate cumulative totals until it reads a zero in Row_1. At that point the cumulative total resets, beginning with the cell containing the zero. "Ron Coderre" wrote: This works for me when my list of values begin in Cell A1. Put this formula in Cell B1 and copy down: =SUMPRODUCT(SUM(INDIRECT("$A$"&MAX(--($A$1:A1=0)*ROW($A$1:A1),1)&":"&CELL("address",A1) ))) Does that help? -- Regards, Ron "Midnight404" wrote: Can someone help? I want to keep a running total of one column but if it encounters a zero in the column then I want the zero to reset the total to zero and continue from that point. Help is appreciated. -- neta |
All times are GMT +1. The time now is 04:14 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com