Sum Column only if matches criteria

[mnoah]

Hi everyone,
This should be an easy one. I have account numbers in column G, and invoice amounts in column F. I want to

1. Insert a new row once there is a new account number.
2. Sub total the invoice amounts in that new row.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Invoice[/TD]
[TD]Account[/TD]
[/TR]
[TR]
[TD]$5[/TD]
[TD]11111[/TD]
[/TR]
[TR]
[TD]$5[/TD]
[TD]11111[/TD]
[/TR]
[TR]
[TD]$10[/TD]
[TD]11111[/TD]
[/TR]
[TR]
[TD]$20[/TD]
[TD]Subtotal[/TD]
[/TR]
[TR]
[TD]$3[/TD]
[TD]22222[/TD]
[/TR]
[TR]
[TD]$3[/TD]
[TD]22222[/TD]
[/TR]
[TR]
[TD]$3[/TD]
[TD]22222[/TD]
[/TR]
[TR]
[TD]$3[/TD]
[TD]22222[/TD]
[/TR]
[TR]
[TD]$12[/TD]
[TD]Subtotal[/TD]
[/TR]
</tbody>[/TABLE]


I have gotten this far with the code here:

Sub InsertRowsAtValueChange()Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next
Set WorkRng = Worksheets("Billing").Range("G4:G1000")


Application.ScreenUpdating = False
For i = WorkRng.Rows.Count To 2 Step -1
    If WorkRng.Cells(i, 1).Value <> WorkRng.Cells(i - 1, 1).Value Then
        'inserts blank row after new account #
        WorkRng.Cells(i, 1).EntireRow.Insert
        'Inserts text for subtotal
        WorkRng.Cells(i, 1).Value = "Sub total"
        'NEED FORMULA TO Sub Total each account
        WorkRng.Cells(i, 0).Value = Application.SumIf(Range("g4:g1000"), Cells(i, 1), Range("F4:f1000"))
        
    End If
Next
Application.ScreenUpdating = True
End Sub

Any help is appreciated!

Thanks

 

[Gerald Higgins]

Have you considered a pivot table ?
They are designed for just this sort of thing.

 

[mnoah]

I haven't, but I am looking to do this calculation within another routine. The file is used by non-Excel minded people and it's part of an email notification system.

 

[mnoah]

Bump...anyone willing to take a stab at this?