Cumulative Total

[Paul Black]

Hi Everyone,

I have three Cells, E21, F21 & G21 in a Sheet named BNT which change
EVERY time F9 is pressed.
What I would like to do is to keep a cumulative total of the three
Cells in F24.

For example, if ...

E21 = 0
F21 = 10
G21 = 20

.... the total of the three Cells would be 30 and would go in Cell F24.
Then F9 is pressed again and could change to ...

E21 = 10
F21 = 30
G21 = 0

.... so the new total of the three Cells would be 40, BUT the cumulative
total in Cell F24 would now change to 70.

Is there a way that every time F9 is pressed it adds Cells E21, F21 &
G21 together and puts it on a Sheet named CUM starting in Cell A1. Then
the next time F9 is pressed it adds Cells E21, F21 & G21 together and
puts it on the Sheet named CUM in Cell A2, then Cell A3 etc. Then I
could sum column A:A in the Sheet named CUM and have the cumulative
total in Cell F24 of the Sheet named BNT.
Or is a better way, to have a Macro attached to a button that does the
above and just updates the value in Cell F24 of the Sheet named BNT.
I do not have the MOREFUNC addin available to me unfortunately.

Many thanks in advance.
All the Best.
Paul

 

[JMB]

Try this: right click on your worksheet containing cells E21:G21, select view
code, and paste this code in the code window that appears. Your formula to
get the cumulative total would be =SUM(Cum!A:A)

Private Sub Worksheet_Calculate()
Dim rngDest As Range
Dim lngTotal As Long

Set rngDest = Sheets("Cum").Range("A1")
lngTotal = Application.Sum(Me.Range("E21:G21"))

If IsEmpty(rngDest) Then
rngDest.Value = lngTotal
Else
With rngDest.Parent
.Cells(.Rows.Count, _
rngDest.Column).End(xlUp)(2, 1).Value = lngTotal
End With
End If

End Sub


If you don't need all of the Intermediate totals, you could shorten it to this

Private Sub Worksheet_Calculate()
With Me.Range("F24")
.Value = .Value + Application.Sum(Me.Range("E21:G21"))
End With
End Sub

 

[Jim Jackson]

I would go the Macro route.
Sub CumTTL()

Dim a, b, c, d
a = Range("F21").Value
b = Range("G21").Value
c = Range("H21").Value
d = Range("F24").Value
Sheets("Codes1").Activate
Range("F24").Activate
d = a + b + c + d
Range("F24") = d

End Sub