VBA Code for Summing Multiple Dynamic Ranges in One Column

F

FrenchCelt

Board Regular
Joined
May 22, 2018
Messages
214
Office Version
  1. 365
Platform
  1. Windows
Hello,

I have a spreadsheet with a column which lists the total of daily output in various categories (each one in a different row) for multiple people, and I want to be able to sum that output in a row at the bottom of each person's summary. I have a Total row inserted between the bottom row for one person and the top row of the next person, and I have the VBA code looking for the cell with Total in the appropriate row and offsetting to the correct cell where I want the sum to appear, but I don't know how to get it to sum the numbers above that only include the person in question. The number of rows involved is different for each person and will also vary for the same person day to day, so I can't use any constants. The only other way to identify which numbers should be included is that the person's name appears in Column A in every cell I need to sum in Column I, so if I can get it to look first at the row with Total, offset 7 cells to Column I, then look up every row with, say, userA in Column A that has a corresponding value in Column I, and sum them up, then I would be set.

Any help would be appreciated.
 
Sort by date Sort by votes
Please don't quote all the posts. Just extra clutter.
Are there empty cells in the data ranges?
 
Upvote 0

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
forgot that data starts on row 5. This code assumes that Column B will have a blank cell between groups of data. If not the code fails.

Code: 
Sub t4()
Dim i As Long, lr As Long, st As Range, fl As Range
With ActiveSheet
    lr = .Cells(Rows.Count, 1).End(xlUp).Row
    For i = lr To 5 Step -1
        If LCase(.Cells(i, 1)) = "total" Then
            Set st = .Cells(i, 2).End(xlUp).Offset(, 7)
            If st.Row < 5 Then Set st = .Cells(5, 9)
            Set fl = .Cells(i - 1, 9)
            .Cells(i, 9) = Application.Sum(Range(st, fl))
            .Cells(i, 10) = Application.Sum(Range(st.Offset(, 1), fl.Offset(, 1)))
            .Cells(i, 11) = Application.Sum.Range(st.Offset(, 2), fl.Offset(, 2))
            .Cells(i, 12) = Application.Sum.Range(st.Offset(, 3), fl.Offset(, 3)) / .Cells(i, 11).Value 'This might need work
        End If
        Set st = Nothing
        Set fl = Nothing
    Next
End With
End Sub
 
Last edited:
Upvote 0
I tried using F8 to step through the code, but nothing happened (and there are over 2,000 rows in the spreadsheet, so I didn't bother to keep going once I saw that none of the data was changing after a few iterations).
 
Upvote 0
couple of typos in the Sum statements. I am testing modification.

Now try it

Code: 
Sub t4()
Dim i As Long, lr As Long, st As Range, fl As Range
With ActiveSheet
    lr = .Cells(Rows.Count, 1).End(xlUp).Row
    For i = lr To 5 Step -1
        If LCase(.Cells(i, 1)) = "total" Then
            Set st = .Cells(i - 1, 2).End(xlUp).Offset(, 7)
            If st.Row < 5 Then Set st = .Cells(5, 9)
            Set fl = .Cells(i - 1, 9)
            .Cells(i, 9) = Application.Sum(Range(st, fl))
            .Cells(i, 10) = Application.Sum(Range(st.Offset(, 1), fl.Offset(, 1)))
            .Cells(i, 11) = Application.Sum(Range(st.Offset(, 2), fl.Offset(, 2)))
            .Cells(i, 12) = Application.Sum(Range(st.Offset(, 3), fl.Offset(, 3))) / .Cells(i, 11).Value 'This might need work
        End If
        Set st = Nothing
        Set fl = Nothing
    Next
End With
End Sub
 
Last edited:
Upvote 0
Here is what my test setup looks like. Does your sheet look similar to this. Also, when looking for the results, start on the bottom row of your sheet.
[TABLE="width: 862"]
<TBODY>[TR]
[TD="class: xl65, width: 87, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, width: 87, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, width: 81, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, width: 81, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, width: 81, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, width: 81, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, width: 81, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, width: 81, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, width: 81, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, width: 81, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, width: 81, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, width: 81, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, width: 81, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, width: 81, bgcolor: transparent"]x
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]hdr
[/TD]
[TD="class: xl65, bgcolor: transparent"]hdr
[/TD]
[TD="class: xl65, bgcolor: transparent"]hdr
[/TD]
[TD="class: xl65, bgcolor: transparent"]hdr
[/TD]
[TD="class: xl65, bgcolor: transparent"]hdr
[/TD]
[TD="class: xl65, bgcolor: transparent"]hdr
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]2
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]2
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]5
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]5
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]4
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]2
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]6
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]8
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]2
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]5
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]2
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]5
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]2
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]4
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]5
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]3
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Total
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]20
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]9
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]17
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1.176471
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]5
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]3
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]4
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]2
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]4
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]4
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]9
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]4
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]5
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Total
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]21
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]4
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]10
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1.2
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]6
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]3
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]3
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]3
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]3
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]3
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]5
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]4
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]3
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]3
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]6
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]3
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]3
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]4
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]2
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]3
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]9
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Total
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]16
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]15
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]11
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]2.454545
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
</TBODY>[/TABLE]
 
Last edited:
Upvote 0
If in Post #3 you had changed this
Code: 
Do Until Cells(j, 9).Value = ""
to this (because of the empty cells)
Code: 
Do Until Cells(j, 1).Value = ""
and had told us about the last "Overall Total" row, you would have been finished.

Maybe try this.
It should sum columns 9, 10 and 11 and Average columns 12 and 14 for each block of individuals.
Code: 
Sub Sum_And_Average_Blocks_Of_Same_Values_Multiple_Columns()
    Dim lr As Long, j As Long, lst As Range, a As String, i As Long, ii As Long
    Dim sumArr, avArr
    sumArr = Array(9, 10, 11)
    avArr = Array(12, 14)
    lr = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
    i = 11
    j = 5
    Application.ScreenUpdating = False
    Do Until Cells(j, 1).Value = ""
        a = Cells(j, 1).Value
        Set lst = Range("A:A").Find(a, After:=Cells(1, 1), SearchDirection:=xlPrevious, LookAt:=xlWhole)
            For i = LBound(sumArr) To UBound(sumArr)
                Cells(lst.Row + 1, sumArr(i)).Value = WorksheetFunction.Sum(Range(Cells(j, sumArr(i)), Cells(lst.Row, sumArr(i))))
            Next i
            For ii = LBound(avArr) To UBound(avArr)
                Cells(lst.Row + 1, avArr(ii)).Value = WorksheetFunction.Average(Range(Cells(j, avArr(ii)), Cells(lst.Row, avArr(ii))))
            Next ii
        j = lst.Row + 2
    Loop
    Application.ScreenUpdating = True
End Sub

If you get an error message, you might have to delete the "Overall Total" Row for now. It can be added in later.
 
Last edited:
Upvote 0
This will be my last shot at it. I only provided for total in columns I, J, K and L. I was not sure how you calculated the others. This uses the areas method so you should see any changes at the top of the sheet for this code.

Code: 
Sub t()
Dim rng As Range, ar As Range, cel As Range
    With ActiveSheet
        Set rng = .Range("B5", .Cells(Rows.Count, 2).End(xlUp))
        For Each ar In rng.SpecialCells(xlCellTypeConstants).Areas
            'MsgBox cel.Address
            Set cel = ar.Columns(1).Cells(1).End(xlDown).Offset(1, 7)
            cel = Application.Sum(.Range(ar.Columns(8).Cells(1), cel.Offset(-1)))
            cel.Offset(, 1) = Application.Sum(.Range(ar.Columns(9).Cells(1), cel.Offset(-1, 1)))
        cel.Offset(, 2) = Application.Sum(.Range(ar.Columns(10).Cells(1), cel.Offset(-1, 2)))
            cel.Offset(, 3) = Application.Sum(.Range(ar.Columns(11).Cells(1), cel.Offset(-1, 3)))
        Next
    End With
End Sub
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question Question
Column Sum problem
Replies
7
Views
166
Peter_SSs
Peter_SSs
D
  • Question Question
Average across multiple worksheets excluding unfilled cells
Replies
4
Views
220
Domenic
D
C
  • Solved
Calculating Totals between two column headers - Macro VBA
Replies
9
Views
123
Fuji
F
A
  • Question Question
How can I create a button to insert a new row and update related formulas?
Replies
0
Views
238
Arlochorim
A
M
  • Solved
vba to sum up each column for each employee on column (A)
Replies
6
Views
341
kvsrinivasamurthy
K

Forum statistics

Threads
1,224,824
Messages
6,181,186
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top