1. ## Count Add And get average

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()
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```  Reply With Quote

2. 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```  Reply With Quote

3. THANK YOU VERY MUCH this macro works great :-)  Reply With Quote

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•