How to make a cumulative sum in one cell?

Question

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.

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
Where would it store the previous value? Excel has no "history" of the values that were previously in A1. — sq33G

Gary's Student Gary's Student · Accepted Answer · 2015-04-21T19:19:55

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:

right-click the tab name near the bottom of the Excel window
select View Code - this brings up a VBE window
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:

bring up the VBE windows as above
clear the code out
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!

How to make a cumulative sum in one cell?

1 Answers