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

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
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
121
Peter_SSs
Peter_SSs
D
  • Question Question
Average across multiple worksheets excluding unfilled cells
Replies
4
Views
160
Domenic
D
A
  • Question Question
How can I create a button to insert a new row and update related formulas?
Replies
0
Views
158
Arlochorim
A
M
  • Solved
vba to sum up each column for each employee on column (A)
Replies
6
Views
314
kvsrinivasamurthy
K
W
  • Question Question
Formatting raw data with extra rows automatically added in-between records
Replies
1
Views
55
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,223,911
Messages
6,175,337
Members
452,637
Latest member
Ezio2866

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