0

I would like the sum the values in one cell into nearby cell cumulatively. It should sum the values, when I typed in the cell. For example whenever I enter a new value to A1 it should sum the total at B1 cell. Is that possible ?

I tried to sum with the formulae

=B1+A1

but i get recursive function error.

Yirmidokuz
  • 127
  • 1
  • 7
  • Are you trying to creating a running total? If so, you will need a slightly different formula in the first cell than the rest of the series -- in B1 just write `=A1` and then in B2 write `=A2+B1` and copy it down the rest of the way. – CactusCake Apr 21 '15 at 19:19
  • Where would it store the previous value? Excel has no "history" of the values that were previously in A1. – sq33G Apr 21 '15 at 19:20

1 Answers1

2

Place the following event macro in the worksheet code area:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Range("A1"), Target) Is Nothing Then Exit Sub
    [B1] = [B1] + [A1]
End Sub

Because it is worksheet code, it is very easy to install and automatic to use:

  1. right-click the tab name near the bottom of the Excel window
  2. select View Code - this brings up a VBE window
  3. paste the stuff in and close the VBE window

If you have any concerns, first try it on a trial worksheet.

If you save the workbook, the macro will be saved with it. If you are using a version of Excel later then 2003, you must save the file as .xlsm rather than .xlsx

To remove the macro:

  1. bring up the VBE windows as above
  2. clear the code out
  3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

and

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm

Macros must be enabled for this to work!

Gary's Student
  • 95,722
  • 10
  • 59
  • 99