Running Total with conditions

M

Midnight404

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

Ron Coderre

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?
 
D

daniel

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

:

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,COLUMN(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.
 
R

Ron Coderre

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
 
D

daniel

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 said:
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 said:
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
??

:

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,COLUMN(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.
Click to expand...
Click to expand...
 
Top