You have a range of cells with values. Then you want to create sum function at the bottom and on the right hand side of the range.

Sum functions are needed in row 6 and column F. This is easy and can be done manually, of course. Just copy the sum to remaining rows and columns.

If you need to do this process repeatedly, a macro might help you. The VBA is added at the end of this blog.

To run the macro, you need to place the cursor in the top left corner of the data range. In our case, the start cell is B2.

Zs is the address of the start cell, eg B2. Then we go at the end of the B row, the address of that cell is stored in zl. Then we go one line down and add the sum sentence SUM(start cell:end cell), then we go one row upwards and one column to right. If that cell is empty then we need to add the sum sentences for each row. If that cell is not empty then we continue counting column sums.

After counting column sums, then we go to the start cell, in this case B2. Zs and zl are populated again. We move to the right end of the data range. Then we move one step to right and there the SUM(start cell:end cell) sentence will be created. Then we move one row down and one column to left. If the cell is empty, then the macro is finished. If the cell is not empty, then we continue counting row sums.

Instead of manually creating sum sentences, place the cursor in B2 and execute the VBA below.

Sub z_sum2()

Dim zs, zl, zss

zss = ActiveCell.Address

Do

zs = ActiveCell.Address

Selection.End(xlDown).Select

zl = ActiveCell.Address

ActiveCell.Offset(1, 0).Select

ActiveCell.Formula = “=sum(” & zs & “:” & zl & “)”

ActiveCell.Offset(-1, 1).Select

If ActiveCell.Value = “” Then

    GoTo zrow

Else

    Selection.End(xlUp).Select

End If

Loop

zrow:

Range(zss).Select

Do

zs = ActiveCell.Address

Selection.End(xlToRight).Select

zl = ActiveCell.Address

ActiveCell.Offset(0, 1).Select

ActiveCell.Formula = “=sum(” & zs & “:” & zl & “)”

ActiveCell.Offset(1, -1).Select

If ActiveCell.Value = “” Then

    Range(zss).Select

    MsgBox “Done.”

    End

Else

    Selection.End(xlToLeft).Select

End If

Loop

End Sub