+ Reply to Thread
Results 1 to 13 of 13

keeping a running total in a single cell

  1. #1
    shrtdawg73
    Guest

    keeping a running total in a single cell

    Is it possible to have numbers added to the same cell and have excel continue
    to calculate the addition for me in that same cell......ex: I have the number
    8 in cell d2 and I want to add the number 8 to that cell and have excel add
    the 8 to the previous 8 for a total of 16 in the same cell.....the next time
    I would add 5, and the total would be 21? Can this be done in a single cell?

  2. #2
    Don Guillett
    Guest

    Re: keeping a running total in a single cell

    right click sheet tab>insert this>SAVE> now in cell a5 you can add it up

    Option Explicit
    Dim oldvalue As Double

    Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Target.Address = "$A$5" Then
    On Error GoTo fixit
    Application.EnableEvents = False
    If Target.Value = 0 Then oldvalue = 0
    Target.Value = 1 * Target.Value + oldvalue
    oldvalue = Target.Value
    fixit:
    Application.EnableEvents = True
    End If
    End Sub


    --
    Don Guillett
    SalesAid Software
    [email protected]
    "shrtdawg73" <[email protected]> wrote in message
    news:[email protected]...
    > Is it possible to have numbers added to the same cell and have excel
    > continue
    > to calculate the addition for me in that same cell......ex: I have the
    > number
    > 8 in cell d2 and I want to add the number 8 to that cell and have excel
    > add
    > the 8 to the previous 8 for a total of 16 in the same cell.....the next
    > time
    > I would add 5, and the total would be 21? Can this be done in a single
    > cell?




  3. #3
    Duke Carey
    Guest

    Re: keeping a running total in a single cell

    While Don's approach can solve your problem, keep in mind that you lose any
    kind of audit trail. If you miskey a number, your running total is hosed and
    there is nothing you can do but start all over. Matter of fact, even if you
    DON'T miskey a number, you hav no way of validating that the running total is
    correct.

    In other words, what you want to accomplish isn't a recommended way of working


    "Don Guillett" wrote:

    > right click sheet tab>insert this>SAVE> now in cell a5 you can add it up
    >
    > Option Explicit
    > Dim oldvalue As Double
    >
    > Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    > If Target.Address = "$A$5" Then
    > On Error GoTo fixit
    > Application.EnableEvents = False
    > If Target.Value = 0 Then oldvalue = 0
    > Target.Value = 1 * Target.Value + oldvalue
    > oldvalue = Target.Value
    > fixit:
    > Application.EnableEvents = True
    > End If
    > End Sub
    >
    >
    > --
    > Don Guillett
    > SalesAid Software
    > [email protected]
    > "shrtdawg73" <[email protected]> wrote in message
    > news:[email protected]...
    > > Is it possible to have numbers added to the same cell and have excel
    > > continue
    > > to calculate the addition for me in that same cell......ex: I have the
    > > number
    > > 8 in cell d2 and I want to add the number 8 to that cell and have excel
    > > add
    > > the 8 to the previous 8 for a total of 16 in the same cell.....the next
    > > time
    > > I would add 5, and the total would be 21? Can this be done in a single
    > > cell?

    >
    >
    >


  4. #4
    shrtdawg73
    Guest

    Re: keeping a running total in a single cell

    thanks for the information, very much appreciated

    "Don Guillett" wrote:

    > right click sheet tab>insert this>SAVE> now in cell a5 you can add it up
    >
    > Option Explicit
    > Dim oldvalue As Double
    >
    > Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    > If Target.Address = "$A$5" Then
    > On Error GoTo fixit
    > Application.EnableEvents = False
    > If Target.Value = 0 Then oldvalue = 0
    > Target.Value = 1 * Target.Value + oldvalue
    > oldvalue = Target.Value
    > fixit:
    > Application.EnableEvents = True
    > End If
    > End Sub
    >
    >
    > --
    > Don Guillett
    > SalesAid Software
    > [email protected]
    > "shrtdawg73" <[email protected]> wrote in message
    > news:[email protected]...
    > > Is it possible to have numbers added to the same cell and have excel
    > > continue
    > > to calculate the addition for me in that same cell......ex: I have the
    > > number
    > > 8 in cell d2 and I want to add the number 8 to that cell and have excel
    > > add
    > > the 8 to the previous 8 for a total of 16 in the same cell.....the next
    > > time
    > > I would add 5, and the total would be 21? Can this be done in a single
    > > cell?

    >
    >
    >


  5. #5
    shrtdawg73
    Guest

    Re: keeping a running total in a single cell

    thanks for the reply

    "Duke Carey" wrote:

    > While Don's approach can solve your problem, keep in mind that you lose any
    > kind of audit trail. If you miskey a number, your running total is hosed and
    > there is nothing you can do but start all over. Matter of fact, even if you
    > DON'T miskey a number, you hav no way of validating that the running total is
    > correct.
    >
    > In other words, what you want to accomplish isn't a recommended way of working
    >
    >
    > "Don Guillett" wrote:
    >
    > > right click sheet tab>insert this>SAVE> now in cell a5 you can add it up
    > >
    > > Option Explicit
    > > Dim oldvalue As Double
    > >
    > > Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    > > If Target.Address = "$A$5" Then
    > > On Error GoTo fixit
    > > Application.EnableEvents = False
    > > If Target.Value = 0 Then oldvalue = 0
    > > Target.Value = 1 * Target.Value + oldvalue
    > > oldvalue = Target.Value
    > > fixit:
    > > Application.EnableEvents = True
    > > End If
    > > End Sub
    > >
    > >
    > > --
    > > Don Guillett
    > > SalesAid Software
    > > [email protected]
    > > "shrtdawg73" <[email protected]> wrote in message
    > > news:[email protected]...
    > > > Is it possible to have numbers added to the same cell and have excel
    > > > continue
    > > > to calculate the addition for me in that same cell......ex: I have the
    > > > number
    > > > 8 in cell d2 and I want to add the number 8 to that cell and have excel
    > > > add
    > > > the 8 to the previous 8 for a total of 16 in the same cell.....the next
    > > > time
    > > > I would add 5, and the total would be 21? Can this be done in a single
    > > > cell?

    > >
    > >
    > >


  6. #6
    shrtdawg73
    Guest

    Re: keeping a running total in a single cell

    Don, again thanks for your help, but I'm having a little trouble following
    what you put. When I right click the sheet tab (at the bottom of the page)
    and select insert, it comes up with a general tab and a spreadsheet solutions
    tab. Does the formula that you wrote below start Option Explicit???? Is
    there a formula that I can just assign to that specific cell......sorry if
    this doesn't make sense.

    "Don Guillett" wrote:

    > right click sheet tab>insert this>SAVE> now in cell a5 you can add it up
    >
    > Option Explicit
    > Dim oldvalue As Double
    >
    > Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    > If Target.Address = "$A$5" Then
    > On Error GoTo fixit
    > Application.EnableEvents = False
    > If Target.Value = 0 Then oldvalue = 0
    > Target.Value = 1 * Target.Value + oldvalue
    > oldvalue = Target.Value
    > fixit:
    > Application.EnableEvents = True
    > End If
    > End Sub
    >
    >
    > --
    > Don Guillett
    > SalesAid Software
    > [email protected]
    > "shrtdawg73" <[email protected]> wrote in message
    > news:[email protected]...
    > > Is it possible to have numbers added to the same cell and have excel
    > > continue
    > > to calculate the addition for me in that same cell......ex: I have the
    > > number
    > > 8 in cell d2 and I want to add the number 8 to that cell and have excel
    > > add
    > > the 8 to the previous 8 for a total of 16 in the same cell.....the next
    > > time
    > > I would add 5, and the total would be 21? Can this be done in a single
    > > cell?

    >
    >
    >


  7. #7
    Don Guillett
    Guest

    Re: keeping a running total in a single cell

    This is NOT a formula. It is a macro. A formula will NOT do what you desire.
    I forgot to include a step
    right click sheet tab>VIEW CODE>copy paste the code as written.
    Although this WILL do what you said, Dukes comment is valid.

    --
    Don Guillett
    SalesAid Software
    [email protected]
    "shrtdawg73" <[email protected]> wrote in message
    news:[email protected]...
    > Don, again thanks for your help, but I'm having a little trouble following
    > what you put. When I right click the sheet tab (at the bottom of the page)
    > and select insert, it comes up with a general tab and a spreadsheet
    > solutions
    > tab. Does the formula that you wrote below start Option Explicit???? Is
    > there a formula that I can just assign to that specific cell......sorry if
    > this doesn't make sense.
    >
    > "Don Guillett" wrote:
    >
    >> right click sheet tab>insert this>SAVE> now in cell a5 you can add it up
    >>
    >> Option Explicit
    >> Dim oldvalue As Double
    >>
    >> Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    >> If Target.Address = "$A$5" Then
    >> On Error GoTo fixit
    >> Application.EnableEvents = False
    >> If Target.Value = 0 Then oldvalue = 0
    >> Target.Value = 1 * Target.Value + oldvalue
    >> oldvalue = Target.Value
    >> fixit:
    >> Application.EnableEvents = True
    >> End If
    >> End Sub
    >>
    >>
    >> --
    >> Don Guillett
    >> SalesAid Software
    >> [email protected]
    >> "shrtdawg73" <[email protected]> wrote in message
    >> news:[email protected]...
    >> > Is it possible to have numbers added to the same cell and have excel
    >> > continue
    >> > to calculate the addition for me in that same cell......ex: I have the
    >> > number
    >> > 8 in cell d2 and I want to add the number 8 to that cell and have excel
    >> > add
    >> > the 8 to the previous 8 for a total of 16 in the same cell.....the next
    >> > time
    >> > I would add 5, and the total would be 21? Can this be done in a single
    >> > cell?

    >>
    >>
    >>




  8. #8
    Gord Dibben
    Guest

    Re: keeping a running total in a single cell

    Are you sure you want to do this?

    Think about it after reading the following.

    You can have a cumulative total in a cell if you have a
    separate source cell for adding a new total to the original.

    Use at your own risk. I am Posting this just to show you how it can
    be done, not as a good solution. You would be much better off to
    have another column so you can keep track of past entries.

    Goes like this: =IF(CELL("address")="$C$4",C4+D4,D4)

    Enter this in cell D4 and then in Tools>Options>Calculation check
    Iterations and set to 1.

    Now when you change the number in C4, D4 will accumulate.

    Note 1. If C4 is selected and a calculation takes place anywhere in
    the Application D4 will update even if no new number is entered in
    C4. NOT GOOD.
    Note 2. This operation is not recommended because you will have no
    "paper trail" to follow. Any mistake in entering a new number in C4
    cannot be corrected. NOT GOOD.

    To clear out the accumulated total in D4 and start over, select D4
    and Edit>Enter.

    Check out Laurent Longre's MoreFunc.xla. Has a Function RECALL
    which does what you want without the re-calculation problem, but
    again there is no "paper trail" for back-checking in case of errors
    in data input.

    http://longre.free.fr/english/func_cats.htm

    Also see John McGimpsey's site for VBA method.

    http://www.mcgimpsey.com/excel/accumulator.html



    Gord Dibben Excel MVP

    On Tue, 22 Aug 2006 12:36:01 -0700, shrtdawg73
    <[email protected]> wrote:

    >Is it possible to have numbers added to the same cell and have excel continue
    >to calculate the addition for me in that same cell......ex: I have the number
    >8 in cell d2 and I want to add the number 8 to that cell and have excel add
    >the 8 to the previous 8 for a total of 16 in the same cell.....the next time
    >I would add 5, and the total would be 21? Can this be done in a single cell?


    Gord Dibben MS Excel MVP

  9. #9
    shrtdawg73
    Guest

    Re: keeping a running total in a single cell

    thanks for the help. I think what you explained is more of what I'm looking
    for.

    if I use the formula " =IF(CELL("address")="$C$4",C4+D4,D4) " (does it go in
    the cell exactly like that) I can add a number to C4 and D4 will reflect the
    total? I hope I'm understanding this right? :o)

    "Gord Dibben" wrote:

    > Are you sure you want to do this?
    >
    > Think about it after reading the following.
    >
    > You can have a cumulative total in a cell if you have a
    > separate source cell for adding a new total to the original.
    >
    > Use at your own risk. I am Posting this just to show you how it can
    > be done, not as a good solution. You would be much better off to
    > have another column so you can keep track of past entries.
    >
    > Goes like this: =IF(CELL("address")="$C$4",C4+D4,D4)
    >
    > Enter this in cell D4 and then in Tools>Options>Calculation check
    > Iterations and set to 1.
    >
    > Now when you change the number in C4, D4 will accumulate.
    >
    > Note 1. If C4 is selected and a calculation takes place anywhere in
    > the Application D4 will update even if no new number is entered in
    > C4. NOT GOOD.
    > Note 2. This operation is not recommended because you will have no
    > "paper trail" to follow. Any mistake in entering a new number in C4
    > cannot be corrected. NOT GOOD.
    >
    > To clear out the accumulated total in D4 and start over, select D4
    > and Edit>Enter.
    >
    > Check out Laurent Longre's MoreFunc.xla. Has a Function RECALL
    > which does what you want without the re-calculation problem, but
    > again there is no "paper trail" for back-checking in case of errors
    > in data input.
    >
    > http://longre.free.fr/english/func_cats.htm
    >
    > Also see John McGimpsey's site for VBA method.
    >
    > http://www.mcgimpsey.com/excel/accumulator.html
    >
    >
    >
    > Gord Dibben Excel MVP
    >
    > On Tue, 22 Aug 2006 12:36:01 -0700, shrtdawg73
    > <[email protected]> wrote:
    >
    > >Is it possible to have numbers added to the same cell and have excel continue
    > >to calculate the addition for me in that same cell......ex: I have the number
    > >8 in cell d2 and I want to add the number 8 to that cell and have excel add
    > >the 8 to the previous 8 for a total of 16 in the same cell.....the next time
    > >I would add 5, and the total would be 21? Can this be done in a single cell?

    >
    > Gord Dibben MS Excel MVP
    >


  10. #10
    Gord Dibben
    Guest

    Re: keeping a running total in a single cell

    Don meant right-click on the sheet tab and "View Code"

    Paste his code into that sheet module.

    I still do not recommend this accumulator method, but caveat emptor.


    Gord Dibben MS Excel MVP

    On Tue, 22 Aug 2006 14:27:02 -0700, shrtdawg73
    <[email protected]> wrote:

    >Don, again thanks for your help, but I'm having a little trouble following
    >what you put. When I right click the sheet tab (at the bottom of the page)
    >and select insert, it comes up with a general tab and a spreadsheet solutions
    >tab. Does the formula that you wrote below start Option Explicit???? Is
    >there a formula that I can just assign to that specific cell......sorry if
    >this doesn't make sense.
    >
    >"Don Guillett" wrote:
    >
    >> right click sheet tab>insert this>SAVE> now in cell a5 you can add it up
    >>
    >> Option Explicit
    >> Dim oldvalue As Double
    >>
    >> Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    >> If Target.Address = "$A$5" Then
    >> On Error GoTo fixit
    >> Application.EnableEvents = False
    >> If Target.Value = 0 Then oldvalue = 0
    >> Target.Value = 1 * Target.Value + oldvalue
    >> oldvalue = Target.Value
    >> fixit:
    >> Application.EnableEvents = True
    >> End If
    >> End Sub
    >>
    >>
    >> --
    >> Don Guillett
    >> SalesAid Software
    >> [email protected]
    >> "shrtdawg73" <[email protected]> wrote in message
    >> news:[email protected]...
    >> > Is it possible to have numbers added to the same cell and have excel
    >> > continue
    >> > to calculate the addition for me in that same cell......ex: I have the
    >> > number
    >> > 8 in cell d2 and I want to add the number 8 to that cell and have excel
    >> > add
    >> > the 8 to the previous 8 for a total of 16 in the same cell.....the next
    >> > time
    >> > I would add 5, and the total would be 21? Can this be done in a single
    >> > cell?

    >>
    >>
    >>



  11. #11
    Gord Dibben
    Guest

    Re: keeping a running total in a single cell

    Yes

    Enter the formula in D4.

    Enter a number in C4, D4 will show that number.

    Overwrite the number in C4 with another and D4 will accumulate.

    You can use any two cells for the input and accumulator.

    Just change the cell references.

    Be sure to read all the caveats included with this method.


    Gord


    On Tue, 22 Aug 2006 15:38:02 -0700, shrtdawg73
    <[email protected]> wrote:

    >thanks for the help. I think what you explained is more of what I'm looking
    >for.
    >
    >if I use the formula " =IF(CELL("address")="$C$4",C4+D4,D4) " (does it go in
    >the cell exactly like that) I can add a number to C4 and D4 will reflect the
    >total? I hope I'm understanding this right? :o)
    >
    >"Gord Dibben" wrote:
    >
    >> Are you sure you want to do this?
    >>
    >> Think about it after reading the following.
    >>
    >> You can have a cumulative total in a cell if you have a
    >> separate source cell for adding a new total to the original.
    >>
    >> Use at your own risk. I am Posting this just to show you how it can
    >> be done, not as a good solution. You would be much better off to
    >> have another column so you can keep track of past entries.
    >>
    >> Goes like this: =IF(CELL("address")="$C$4",C4+D4,D4)
    >>
    >> Enter this in cell D4 and then in Tools>Options>Calculation check
    >> Iterations and set to 1.
    >>
    >> Now when you change the number in C4, D4 will accumulate.
    >>
    >> Note 1. If C4 is selected and a calculation takes place anywhere in
    >> the Application D4 will update even if no new number is entered in
    >> C4. NOT GOOD.
    >> Note 2. This operation is not recommended because you will have no
    >> "paper trail" to follow. Any mistake in entering a new number in C4
    >> cannot be corrected. NOT GOOD.
    >>
    >> To clear out the accumulated total in D4 and start over, select D4
    >> and Edit>Enter.
    >>
    >> Check out Laurent Longre's MoreFunc.xla. Has a Function RECALL
    >> which does what you want without the re-calculation problem, but
    >> again there is no "paper trail" for back-checking in case of errors
    >> in data input.
    >>
    >> http://longre.free.fr/english/func_cats.htm
    >>
    >> Also see John McGimpsey's site for VBA method.
    >>
    >> http://www.mcgimpsey.com/excel/accumulator.html
    >>
    >>
    >>
    >> Gord Dibben Excel MVP
    >>
    >> On Tue, 22 Aug 2006 12:36:01 -0700, shrtdawg73
    >> <[email protected]> wrote:
    >>
    >> >Is it possible to have numbers added to the same cell and have excel continue
    >> >to calculate the addition for me in that same cell......ex: I have the number
    >> >8 in cell d2 and I want to add the number 8 to that cell and have excel add
    >> >the 8 to the previous 8 for a total of 16 in the same cell.....the next time
    >> >I would add 5, and the total would be 21? Can this be done in a single cell?

    >>
    >> Gord Dibben MS Excel MVP
    >>



  12. #12
    Registered User
    Join Date
    05-25-2015
    Location
    Portsmouth
    MS-Off Ver
    2013
    Posts
    5

    Re: keeping a running total in a single cell

    Hi I have used this code

    Option Explicit
    Dim oldvalue As Double

    Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Target.Address = "$A$5" Then
    On Error GoTo fixit
    Application.EnableEvents = False
    If Target.Value = 0 Then oldvalue = 0
    Target.Value = 1 * Target.Value + oldvalue
    oldvalue = Target.Value
    fixit:
    Application.EnableEvents = True
    End If
    End Sub

    And it works perfectly.

    However on the same sheet I don't just need it to happen for A5.

    I need the following cells to also do the same;

    C5:C23
    H5:H22
    M5:M28

    I want these to accumulate individually, not as columns, after each user input.

    Apologies for the lack of knowledge this one really has me stumped.

    Thanks

    lpsp1994
    Last edited by lpsp1994; 05-25-2015 at 06:23 AM.

  13. #13
    Registered User
    Join Date
    07-26-2015
    Location
    Patna, India
    MS-Off Ver
    2010
    Posts
    1

    Re: keeping a running total in a single cell

    Hello,

    Once the code is saved and the workbook closed.
    You enter the value in the respective cell the summation does not take place.
    it takes the current value you enter.
    ----I want the previous values before closing the file to add up to the values when I open it again.
    ---- That is the total should carry forward from where I left after closing the file and opening it again to add the inputs
    PASTING THE CODE I USED --- //

    Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Static dAccumulator_1 As Double
    Static dAccumulator_2 As Double
    Static dAccumulator_3 As Double
    Static dAccumulator_4 As Double
    Static dAccumulator_5 As Double
    With Target
    If .Address(False, False) = "B6" Then
    If Not IsEmpty(.Value) And IsNumeric(.Value) Then
    dAccumulator_1 = dAccumulator_1 + .Value
    Else
    dAccumulator_1 = 0
    End If
    Application.EnableEvents = False
    .Value = dAccumulator_1
    Application.EnableEvents = True
    End If
    End With
    With Target
    If .Address(False, False) = "B7" Then
    If Not IsEmpty(.Value) And IsNumeric(.Value) Then
    dAccumulator_2 = dAccumulator_2 + .Value
    Else
    dAccumulator_2 = 0
    End If
    Application.EnableEvents = False
    .Value = dAccumulator_2
    Application.EnableEvents = True
    End If
    End With
    With Target
    If .Address(False, False) = "B8" Then
    If Not IsEmpty(.Value) And IsNumeric(.Value) Then
    dAccumulator_3 = dAccumulator_3 + .Value
    Else
    dAccumulator_3 = 0
    End If
    Application.EnableEvents = False
    .Value = dAccumulator_3
    Application.EnableEvents = True
    End If
    End With
    With Target
    If .Address(False, False) = "B9" Then
    If Not IsEmpty(.Value) And IsNumeric(.Value) Then
    dAccumulator_4 = dAccumulator_4 + .Value
    Else
    dAccumulator_4 = 0
    End If
    Application.EnableEvents = False
    .Value = dAccumulator_4
    Application.EnableEvents = True
    End If
    End With
    With Target
    If .Address(False, False) = "B11" Then
    If Not IsEmpty(.Value) And IsNumeric(.Value) Then
    dAccumulator_5 = dAccumulator_5 + .Value
    Else
    dAccumulator_5 = 0
    End If
    Application.EnableEvents = False
    .Value = dAccumulator_5
    Application.EnableEvents = True
    End If
    End With
    End Sub
    Last edited by chanchalss; 08-11-2015 at 11:57 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1