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
My Problem Solving Blog 