Count Add And get average

[martinez_pedro]

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

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

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

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

 

[Bob Phillips]

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

 

[martinez_pedro]

THANK YOU VERY MUCH this macro works great