Progressive Count

Does anyone have a word for this formula?

Hi ChemistB

What if the first count is store, let said in A12 and then every time i clear content in A1;A10 and fill again to have a progressive count, let said in A13, is this possible?

I'm new to excel and never done a VBA but i will like to give it a try. Any advantage or disadvantage with VBA

Thanks to both of you but i am struggling here. I have never made a macro before and can't get it to work, Any advice on how to make it right? i don't know what i am doing wrong...i have a mac power book & excel 2004. Just in case of any misunderstanding, I need the macro to count cells with values > 0 not to sum the values.

Regards

Hi Colin Legg,
Thank you very much, it does what i need but for some reason is counting 11 cells instead of 10 cell, it is possible to fix this and not to count cells that have 0? but instead of cells A1:A10 need it to count cells X9:X48 and keep the count in Y7

Regards

Hi Rob, Colin

Sorry, I do not explained clearly, (X9:X48) is the range where i need the progressive count from, but instead of typing the value on the range ( X9:X48 ) the value need to be retrieved from ( L16:L25 ), ( T16:T25 ), ( L32:L41) and ( T32:T41)

It is possible to change the code to do that?
Thanks

Hi Rob, thanks for getting back to me, yes i still need some help, that's why i have not marked Solved.
The code Colin made did exactly what i asked him, but instead of me typing the values on the (X9:X48) range the code needs to retrieve the values from ( L16:L25 ), ( T16:T25 ), ( L32:L41) and ( T32:T41).
For better reference I have attached the file with the code Colin made, i also copied the code on sheet 2.
Find notes on sheet 1 on cell (Y15)

Any help is greatly appreciated, thanks in advance

score,

Give this version of the code a try:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim rngToCheck As Range: Set rngToCheck = Me.Range("L16:L25,T16:T25,L32:L41,T32:T41")
    
    If Not Intersect(rngToCheck, Target) Is Nothing Then
        Application.EnableEvents = False
        Dim rngCell As Range
        For Each rngCell In Target
            If Not Intersect(rngCell, rngToCheck) Is Nothing _
            And IsNumeric(rngCell.Value) Then
                Me.[Y7].Value = Me.[Y7].Value + rngCell.Value
            End If
        Next rngCell
        Application.EnableEvents = True
    End If
    
End Sub

Hope that helps,
~tigeravatar

Hi Tigeravatar

I am new to macros, may i did not do the right thing, i opened visual basic editor and copy paste your code to replace the one i had from Colin with no luck, i don't know if i did something wrong or if the code its not doing its thing... any advice?

score,

Sorry, didn't realize those ranges were formulas, not where you enter numbers. I shoulda looked it a little longer. Here's revised code and a modified version of your workbook so you can see how it works:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim rngToCheck As Range: Set rngToCheck = Me.Range("G16:K25,O16:S25,G32:K41,O32:S41")
    
    If Not Intersect(rngToCheck, Target) Is Nothing Then
        
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        
        Dim ChangedSum As Double: ChangedSum = 0
        Dim grp As Range, RowIndex As Integer
        For Each grp In rngToCheck.Areas
            If Not Intersect(Target, grp) Is Nothing Then
                For RowIndex = grp.Row To grp.Row + grp.Rows.Count - 1
                    If Not Intersect(Range(Cells(RowIndex, grp.Column), Cells(RowIndex, grp.Column + grp.Columns.Count - 1)), Target) Is Nothing _
                    And IsNumeric(Cells(RowIndex, grp.Column + grp.Columns.Count).Value) Then
                        ChangedSum = ChangedSum + Cells(RowIndex, grp.Column + grp.Columns.Count).Value
                    End If
                Next RowIndex
            End If
        Next grp
        
        Me.[Y7].Value = Me.[Y7].Value + ChangedSum
        
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    
    End If
    
End Sub

Hope that helps,
~tigeravatar

Hi Tigeravatar

I appreciate your help but that is not the result i need, the result should be 2 in cell Y7,(count cells on range X9:X48). let me try to explain it better, if you notice, on the box named yellow there is only two waves been scored (wave count), each judge gives a score (5) judges, (on L16 is the score for that 1st wave after taking the high and low and averaging the other 3 judges) the wave score given on (L16:L25),(T16:T25),(L32:L41),(T32:T41) is deleted when time is over and a new person get scores, i need those scores to show on the range X9:X48 and keep a progressive cell count of that range, the final results is the total amount of waves surfed at the end of the event (CELL R46)

score,

So the output should be to X9:X48?

Yes, output from X9:X48... and the values on range X9:x18 should be retrieved from L16:L25, the values on range X19:X28 retrieved from T16:T25, the values on range X29:X38 retrieved from L32:L41, the values on range X39:X48 retrieved from T32:T41...hope this explained

score,

Updated code (see attached workbook):

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim rngToCheck As Range: Set rngToCheck = Me.Range("G16:K25,O16:S25,G32:K41,O32:S41")
    
    If Not Intersect(rngToCheck, Target) Is Nothing Then
        
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        
        Dim grp As Range, rngDest As Range
        Dim RowIndex As Integer, AdjustIndex As Integer
        For Each grp In rngToCheck.Areas
            If Not Intersect(Target, grp) Is Nothing Then
                Select Case Left(grp.Address, InStr(grp.Address, ":") - 1)
                    Case "$G$16": AdjustIndex = 0
                    Case "$O$16": AdjustIndex = 10
                    Case "$G$32": AdjustIndex = 20
                    Case "$O$32": AdjustIndex = 30
                End Select
                For RowIndex = grp.Row To grp.Row + grp.Rows.Count - 1
                    If Not Intersect(Range(Cells(RowIndex, grp.Column), Cells(RowIndex, grp.Column + grp.Columns.Count - 1)), Target) Is Nothing _
                    And IsNumeric(Cells(RowIndex, grp.Column + grp.Columns.Count).Value) Then
                        Set rngDest = Me.[X9].Offset(RowIndex - grp.Row + AdjustIndex, 0)
                        rngDest.Value = rngDest.Value + Cells(RowIndex, grp.Column + grp.Columns.Count).Value
                    End If
                Next RowIndex
            End If
        Next grp
        
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    
    End If
    
End Sub

Hope that helps,
~tigeravatar

Hi Tigeravatar
Is not doing what i looking for, i know is possible for excel do what i need, may i have not explain well...
here may is another way to get what i need....i need to count the range of cells with values (L16:L25),(T16:T25),(L32:L41),(T32:T41) the total count need to stay when i clear judges points and then keep a progressive count, the count could be on cell Y7