Count Add And get average

martinez_pedro

New member
Joined
May 12, 2011
Messages
2
Reaction score
0
Points
0
hi so i have this report that i have to do all the time manually i was wondering if anybody can help me modify this macro to be able to add and get an average
the macro to make the count works perfectly i just don't know how to modify it to add and average
thank you for all you help

Code:
Sub Count()
'this will count how many times the client name apears
For lRows = ActiveSheet.UsedRange.Rows.Count To 2 Step -1
If Cells(lRows, 2).Value <> Cells(lRows - 1, 2).Value Then
Cells(lRows, 7).Value = importCounter
importCounter = 1
Else
'Cells(lRows, 1).EntireRow.Delete
importCounter = importCounter + 1
End If
Next lRows
End Sub
Code:
Sub add()
'this one will add Principal received from client
For lRows = ActiveSheet.UsedRange.Rows.Count To 2 Step -1
If Cells(lRows, 5).Value <> Cells(lRows - 1, 5).Value Then
Cells(lRows, 8).Value = importCounter
importCounter = 1
Else
'Cells(lRows, 1).EntireRow.Delete
importCounter = importCounter + 1
End If
Next lRows
End Sub
Code:
Sub average ()
'this one will get the average amount Principal received from client
For lRows = ActiveSheet.UsedRange.Rows.Count To 2 Step -1
If Cells(lRows, 5).Value <> Cells(lRows - 1, 5).Value Then
Cells(lRows, 9).Value = importCounter
importCounter = 1
Else
'Cells(lRows, 1).EntireRow.Delete
importCounter = importCounter + 1
End If
Next lRows
End Sub
 

Attachments

  • SAMPLE DATA.xlsx
    47.8 KB · Views: 11

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
Code:
Sum Aggregations()
Const FORMULA_COUNT = "=IF($B2=$B1,"""",COUNTIF($B$2:$B<lastrow>,$B2))"
Const FORMULA_SUM = "=IF($B2=$B1,"""",SUMIF($B$2:$B<lastrow>,$B2,$E$2:$E<lastrow>))"
Const FORMULA_AVERAGE = "=IF($B2=$B1,"""",AVERAGEIF($B$2:$B<lastrow>,$B2,$E$2:$E<lastrow>))"
Dim lRows As Long


    Application.ScreenUpdating = False

    'this will count how many times the client name apears
    With ActiveSheet
    
        lRows = .Cells(.Rows.Count, "B").End(xlUp).Row
        
        .Range("G2").Resize(lRows - 1, 3).NumberFormat = "General"
        
        .Range("G2").Resize(lRows - 1).Formula = Replace(FORMULA_COUNT, "<lastrow>", lRows)
        .Range("H2").Resize(lRows - 1).Formula = Replace(FORMULA_SUM, "<lastrow>", lRows)
        .Range("I2").Resize(lRows - 1).Formula = Replace(FORMULA_AVERAGE, "<lastrow>", lRows)
        
        With .Range("G2").Resize(lRows - 1, 3)
        
            .Value = .Value
            .NumberFormat = "#,##0"
        End With
    End With

    Application.ScreenUpdating = True
End Sub
 
Last edited:
Top