counting occurances

  • Thread starter Thread starter neilb514
  • Start date Start date
N

neilb514

Hello
I have a list of 13 numbers that change often. I need to find a way to keep
a running total of how many times a specific number occurs, e.g If zero
appears twice at a given time I need to add this to a specific cell, if then
zero appears nowhere it should still say 2, if zero appears once again it
should add to the total so it says 3 etc.
I tried =countif(a1:a13,"0") but as the list changes it wont keep a total.
ie if there are 4 occurances it will say 4 but if the list changes to 1, I
need the total to say 5.

Any help will be much appreciated.
 
Right click sheet tab>view code>copy/paste this>save workbook
Now when you enter a number in the range the cell to the right will
increment.

Private Sub Worksheet_Change(ByVal target As Excel.Range)
If Intersect(target, Range("a1:a13")) Is Nothing _
Or IsNumeric(target) = False Then Exit Sub

Application.EnableEvents = False
target.Offset(, 1) = target.Offset(, 1) + target
Application.EnableEvents = True
End Sub
 
Back
Top