cumulative totals

D

dcaputo

I have a worksheet like this:
/ A / B / C /
/ 1 / water per day / cumulative water / beginning water /
2 / 21 / 71 / 50 /

I want a running sum in column B. In other words, I want cumulativ
water to start out at 0 (B2). when I enter 21 in cell A2, I want it t
add water per day to the beginnig water (C2)(21+50=71) and display tha
number in column B. Then the next day I enter a number in A2, say 15,
want B2 to come out to 86 (71 from the previous day plus the 15 fo
today). I will be entering a "water per day" everyday and want th
running sum displayed in B2.
Thanks for your help!!
 
D

Don Guillett

Seed cell b2 with the first value. Then, in A2 this will do it auotmatically
right click sheet tab>view code>copy/paste this

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$A$2" Then Exit Sub
x = Cells(Rows.Count, "b").End(xlUp).Row
Cells(x + 1, "b").Value = Target + Cells(x, "b")
End Sub
 
D

Domenic

=IF(N(A2),C2+SUM(A2:INDEX(A:A,MATCH(9.99999999999999E+307,A:A))),0)

Hope this helps
 
R

Raymond

Domenic,

I think your formula may be adaptible to my problem but I cannot figure out
exactly how it works and what it does. Can you help me?

My problem is:

I have Sales$ by Agent and CatNo (many Agents and many CatNos).

I want to sort the data by Agent/Sales$ (desc) and calculate the the
Cumulative Sales$ for each Agent from largest sales CatNo to smallest and
repeat the calcaulation for all Agents.

I can copy the formula = sum(c$2:c2) from the first CatNo to the last to
accomplish this for a single Agent but would have to restart the formula at
the start of each new Agent and copy it down for his CatNos each time - far
too tedious for a couple of hundred Agents.

Will your formula solve my problem?

Thanks, Raymond
 
R

Raymond

Domenic,

I think your formula (below) may help me solve a nagging problem but I
cannot figure out how it works or how to use it. Can you help?

My problem is:

I have Sale$ by Agent by CatNo (many Agents and many CatNos). The data are
sorted by Agent/Sales$ (desc).

I want to calc the cumulative Sales$ for each Agent starting at the Agent's
first CatNo through his last CatNo.

Of course, I can do this one Agent at a time by entering =sum(c$2:c2) at
the first record for the first Agent and copying it down for his CatNos. To
calc the second Agent, the formula would have to be restarted at the address
of his first CatNo and be copied down for his data and so on. Far too
tedious for several hundred Agents.

Will your indexing formula help or do you have a suggestion that will?

Thanks, Raymond
 
R

Raymond

Domenic,

I think your formula (below) may help me solve a nagging problem but I
cannot figure out how it works or how to use it. Can you help?

My problem is:

I have Sale$ by Agent by CatNo (many Agents and many CatNos). The data are
sorted by Agent/Sales$ (desc).

I want to calc the cumulative Sales$ for each Agent starting at the Agent's
first CatNo through his last CatNo.

Of course, I can do this one Agent at a time by entering =sum(c$2:c2) at
the first record for the first Agent and copying it down for his CatNos. To
calc the second Agent, the formula would have to be restarted at the address
of his first CatNo and be copied down for his data and so on. Far too
tedious for several hundred Agents.

Will your indexing formula help or do you have a suggestion that will?

Thanks, Raymond
 
D

Domenic

Assuming that Column A contains the Agent Name, Column C contains th
Sales Amount, and your data starts on the second row...

C2, copied down:

=SUMIF($A$2:A2,A2,$C$2:C2)

Hope this helps!
Domenic,

I think your formula (below) may help me solve a nagging problem but I
cannot figure out how it works or how to use it. Can you help?

My problem is:

I have Sale$ by Agent by CatNo (many Agents and many CatNos). Th
data are
sorted by Agent/Sales$ (desc).

I want to calc the cumulative Sales$ for each Agent starting at th
Agent's
first CatNo through his last CatNo.

Of course, I can do this one Agent at a time by entering =sum(c$2:c2
at
the first record for the first Agent and copying it down for hi
CatNos. To
calc the second Agent, the formula would have to be restarted at th
address
of his first CatNo and be copied down for his data and so on. Far too
tedious for several hundred Agents.

Will your indexing formula help or do you have a suggestion that will?

Thanks, Raymond
Click to expand...
 
Top