Forum Discussion

DianaRato's avatar
DianaRato
Copper Contributor
Jun 25, 2021
Solved

Count every time a new value is entered in a Cell

Hello all,

 

I hope I am able to explain what I want to do properly, so here it goes:

 

I have two cells that I want to count the values of, but the second cell is constantly being updated with a new value (a date). I want to count in a third cell the value on the first cell plus the number of times a new value was put in the second cell. So for example, there is a date in the first cell, the second cell had a date as well one day, two days later another date is put in that cell, the third cell should have a final count of 3 (instead of 2 which would happen if I used the COUNT formula).

 

I honestly don't know if this is possible but if it isn't any advice would be appreciated.

 

Thank you!

excel
Excel for web
Formulas and Functions
  • PReagan's avatar
    PReagan
    Jun 25, 2021

    Hello DianaRato,

     

    Enter this VBA code as a procedure in the necessary worksheet:

    Private Sub Worksheet_Change(ByVal Target As Range)
      If Not Intersect(Sheets("Sheet1").Range("A1"), Target) Is Nothing Then
        Range("B1").Value = Range("B1").Value + 1
      End If
    End Sub

     Where...

    • "Sheet1" is the sheet that you want to reference
    • Range("A1") is the range of cells that you want to check for changes
    • Range("B1") is the cell that will add one for each change that is made in the range of cells that you want to check for changes

6 Replies

    • DianaRato's avatar
      DianaRato
      Copper Contributor
      Jun 25, 2021
      Hi Sergei,

      Here is a video, in the third cell I want to appear 3 for the total number of values that were put in those cells.

      https://www.loom.com/share/85e7528b1d7142f988b45218b82244a5
      • PReagan's avatar
        PReagan
        Bronze Contributor
        Jun 25, 2021

        Hello DianaRato,

         

        Enter this VBA code as a procedure in the necessary worksheet:

        Private Sub Worksheet_Change(ByVal Target As Range)
          If Not Intersect(Sheets("Sheet1").Range("A1"), Target) Is Nothing Then
            Range("B1").Value = Range("B1").Value + 1
          End If
        End Sub

         Where...

        • "Sheet1" is the sheet that you want to reference
        • Range("A1") is the range of cells that you want to check for changes
        • Range("B1") is the cell that will add one for each change that is made in the range of cells that you want to check for changes

Resources