Ok... looking at your sample, the sum of salaries (assuming the "Quantity" may not always to be 1) for column B is
Formula:
=SUMPRODUCT($B$2:$B$50,$C$2:$C$50)
and the column average will be:
Formula:
=SUMPRODUCT($B$2:$B$50,$C$2:$C$50/COUNT($B$2:$B$50))
Having that in mind, you could run a VBA routine like this to complete all columns in one fell swoop:
Sub Copy_Formulas()
On Error GoTo error_handler
Application.ScreenUpdating = False
Application.Calculation = xlManual
Application.EnableEvents = False
Dim lCol As Long: lCol = Cells.Find("*", , xlValues, , xlByColumns, xlPrevious).Column
For i = 2 To lCol Step 2
Cells(51, i).Formula = "=SUMPRODUCT(" & Cells(2, i).Address & ":" & Cells(50, i).Address & "," & Cells(2, i + 1).Address & ":" & Cells(50, i + 1).Address & ")"
Cells(52, i).Formula = "=SUMPRODUCT(" & Cells(2, i).Address & ":" & Cells(50, i).Address & "," & Cells(2, i + 1).Address & ":" & Cells(50, i + 1).Address & "/COUNT(" & Cells(2, i).Address & ":" & Cells(50, i).Address & "))"
Next i
error_handler:
Select Case Err.Number
Case 0
Case Else
MsgBox Err.Description & vbCrLf & vbCrLf & "Error no. " & Err.Number
End Select
Application.ScreenUpdating = True
Application.Calculation = xlAutomatic
Application.EnableEvents = True
End Sub
Is that what you had in mind?
Tim
Bookmarks