Sum every cell in nth column with dynamic rows and columns

Question

I have a sheet that contains a variable number of rows and columns. The sheet begins with four standard columns, then additional groups of eight columns, depending on the time the report is drawn.

I need to sum the 2nd, 3rd, 4th, and 5th column cells across the groups of eight to arrive at the sums of the 2nds, 3rds, 4ths, and 5ths column cell entries, for each row.

I sense I have to add these values to an array using a loop with Step. I'm having trouble conceptualizing it.

Answer 1

You could get it with VBA code.

• Using an array(aCol) to keep the columns index is not necessary in your case because it is continous and sequential. It makes the code more flexible for other projects. i.e. you have to get the result for column 1,4,5,8 with the same logic.

Sub demo()
    Dim arr, res(), aCol, i, j, k
    Dim iRow As Long, iCol As Integer
    arr = [a1].CurrentRegion.Value
    iRow = UBound(arr)
    iCol = UBound(arr, 2)
    ReDim res(1 To iRow, 1 To 4)
    aCol = Array(2, 3, 4, 5)
    For j = 0 To UBound(aCol)
        res(1, j + 1) = "SUM-COL" & aCol(j)
    Next
    For i = 2 To UBound(arr)
        For j = 0 To UBound(aCol)
            For k = 5 To iCol Step 8
                res(i, j + 1) = res(i, j + 1) + arr(i, k + aCol(j) - 1)
            Next
        Next
    Next
    Cells(1, iCol + 2).Resize(iRow, 4).Value = res
End Sub