Calculating totals per column, per row, with a macro

Question

I need some help with VBA. I have this sheet:

Game     Room     City          Machines_week_1     Played_week_1     Won_week_1     Machines_week_2     Played_week_2     Won_week_2
A        Julia   San Francisco     3                    1200            1100             2                   500              250
B        Julia   San Francisco     4                    200            100             1                   500              250
C        Julia   San Francisco     3                    500            100             5                   600              250
D        Julia   San Francisco     1                    200            50             2                   1000              750
B        Carla   San Francisco     1                    1000            600             2                   1500              500
C        Carla   San Francisco     3                    800            400             3                   500              250
E        Carla   San Francisco     6                    1200            1100             4                   600              300
A        Jaime   Rio de Janeiro     1                    200            50             2                   90              50
C        Jaime   Rio de Janeiro     3                    900            600             3                   500              250
(....)

And I want to calculate the totals per City and Game, ignoring Rooms, for each week in the end of the sheet. Something like:

Game     Room     City          Machines_week_1     Played_week_1     Won_week_1     Machines_week_2     Played_week_2     Won_week_2
 (...)
A        Total   San Francisco     3                    1200            1100             2                   500              250
B        Total   San Francisco     5                    1200            700             3                   3000              750
C        Total   San Francisco     6                    1300            500             8                   1100              500
D        Total   San Francisco     1                    200            50             2                   1000              750
E        Total   San Francisco     6                    1200            1100             4                   600              300
A        Total   Rio de Janeiro     1                    200            50             2                   90              50
C        Total   Rio de Janeiro     3                    900            600             3                   500              250
(....)

I'm trying something like:

For p = 1 To LastRow

If Range("CA1").Offset(i, 0).Value <> 2 Then ( I have this much of columns with data and weeks)
a = Range("A1").Offset(p, 0).Value
c = Range("C1").Offset(p, 0).Value


For i = 1 To LastRow
    If Range("A1").Offset(i, 0).Value = a And Range("C1").Offset(i, 0).Value = c Then

    Machines1 = Range("D1").Offset(i, 0).Value + Machines1
    Played1 = Range("E1").Offset(i, 0).Value + Played1
    (....)
    Machines2 =  Range("G1").Offset(i, 0).Value + Machines2
    Played2 = Range("H1").Offset(i, 0).Value + Played2
    Range("CA1").Offset(i, 0).Value = 2 (this column is empty, Im using it for a test to see if I have calculated this combination of city and game already)
    End If

    Next i

       lastn = Range("A65536").End(xlUp).Row
        Range("A1").Offset(lastn, 0).Value = a
        Range("B1").Offset(lastn, 0).Value = "Total"
        Range("C1").Offset(lastn, 0).Value = c
        Range("D1").Offset(lastn, 0).Value = Machines1
        Range("E1").Offset(lastn, 0).Value = Played1
        (...)
        Range("G1").Offset(lastn, 0).Value = Machines2
        Range("H1").Offset(lastn, 0).Value = Played2
        (...)

        Machines1=0
        Played1=0
        Machines2=0
        Played2 = 0
        (....)
        End If

        Next p

But it's not working. It's still repeating games for each room (the totals are correct, but they appear as many times as there are rooms in the City). If anyone can help me, I'd apreciate so much. Thank you!

Answer 1

As an alternate method you can use the PivotTable functionality in Excel:

Data used:

PivotTable output: