How do I keep a running tally in excel using a formula?

Question

I am currently working on a project for work and need to have a counter/tally based on the number inputted in another cell, but I want that number to be retained and added to each time that number is changed.

(e.g. User enters 2 into cell E7, that 2 is then displayed in cell K5. Then the user clears E7 and enters 5. K5 should now display 7 since it retained the last value and is adding the new value to it.)

You'll need to use a VB macro for this. Try recording your own, this is a really simple task. Setup your sheet the way you want, then at the time you want the user to "click the button", click "Record macro", then do the steps you want to do manually .. and click stop. View the macro code, and you'll need to make some small changes, however, the basic logic, and more importantly, the syntax for addressing the other cells should be there for you :) If you still need help from there, post what you end up with, and we can help further. Cheers — Ditto, Jun 13 '16 at 15:14
if you are willing to accept VBA code then you should add the tags excel-vba and/or vba to your question — Forward Ed, Jun 13 '16 at 18:32

score 2 · Accepted Answer · answered Jun 13 '16 at 15:30

2

Got it to work by doing VBA and I assigned it as a macro with a CTRL + shortcut. Works like a charm.

Sub Counter() Range("K5") = Range("K5") + Range("E6") End Sub

answered Jun 13 '16 at 15:30

ItalianStallion4215

139
2
11

Just spent a little while doing this myself, and got it working too. I just learned something - thank you! – John Burger Jun 13 '16 at 15:38
2

If you place your code in the worksheet module and have it fire when a value changes on the sheet you can do without the shortcut key: `Private Sub Worksheet_Change(ByVal Target As Range):If Target.Address = "$E$6" And IsNumeric(Target) Then:Range("K5") = Range("K5") + Range("E6"):End If:End Sub` (split the code into lines using `:` as a delimiter). – Darren Bartrup-Cook Jun 13 '16 at 15:43
That is awesome thank you, but for this particular use I don't need that functionality, but will keep in mind for next time. – ItalianStallion4215 Jun 13 '16 at 16:16
@ItalianStallion4215 Good job working out your own answer. – Forward Ed Jun 13 '16 at 18:34

score 0 · Answer 2 · answered Jun 13 '16 at 15:05

0

Sorry, but that's simply not possible with Excel. At best, you can only keep all of the previous values in rows, and add them up.

answered Jun 13 '16 at 15:05

John Burger

3,662
1
13
23

How about in VB? Is there an easy way I can do it via VB and just create a shortcut macro that I can use to run it? – ItalianStallion4215 Jun 13 '16 at 15:07
Your answer is half right john, It cannot be done with Excel FORMULAS, but it can be done with Excel's VBA. – Forward Ed Jun 13 '16 at 18:33

How do I keep a running tally in excel using a formula?

2 Answers2