running total by date (not sorted by date though...)

[rainxking]

Is it possible to have a running total go by date while not sorted by
date?
I would like to change the date but not change the sorting so that the
running total of units is accurate at that period in time. SO basically
I can see how many units I have at anygiven time. I hope this makes
sense. I am sure someone has had to do this before...

Below are two example tables.


Example (sorted by Account):
Acct---Date------units-runnning total
A-----5/01/2006---10-----25
B-----4/01/2006---10-----0
C-----4/15/2006---15-----10
D-----5/15/2006---20-----35
Final Count----55

Example (sorted by Date):
Acct---Date------units-runnning total
A-----4/01/2006---10-----0
B-----4/15/2006---15-----10
C-----5/01/2006---10-----25
D-----5/15/2006---20-----35
Final Count----55

Thank you!!!

 

[Guest]

You may use the following array formula:
=SUM($C$1:$C$4*($B$1:$B$4<$B1))
Change the ranges as appropiate (here B column are dates, and C column
units), and enter it with CRTL+SHIFT+ENTER

Hope this helps,
Miguel.

 

[Bernard Liengme]

This seems to work
IN B1 enter =SUMPRODUCT(--($B$1:$B$10<B1),$C$1:$C$10)
Copy down the column
change B10, and C10 to fit you needs
best wishes

 

[rainxking]

I think I maynot have explained this clearly... Here us a better example
what I have and what I am trying to do... I have units leaving and and
coming in. I need a way to allocate. Going by the table below... I want
to be able to change Joe's ShipDate to 6/25 and see how it affects the
OnHand Units. This way I can look at everything, change a few ship
dates and be able to accomidate more customers. Make Sense? Thanks
again for all the help... If I can get this working it will save me
hours and hours of work.


Rep ShipDate Qty OnHand
Joe 6/9/06 -1128 1200
Matt 6/9/06 -372 72
Jim 6/15/06 -150 -300
John 6/15/06 -6 -450
Sam 6/15/06 -12 -456
DELIVERY 6/15/06 4210 -468
Adam 6/25/06 -288 4678
Joe 6/25/06 -897 4390

 

[rainxking]

Is something I need to look into access to accomplish?