I think this will do what you want
Sub test()
Dim SourceRange As Range
Dim DestinationRange As Range
Dim i As Long
Dim strForm0 As String
Set DestinationRange = Sheet3.Range("a1"): Rem adjust
With Sheet1.Range("A:A")
Set SourceRange = Range(.Cells(1, 4), .Cells(Rows.Count, 1).End(xlUp))
End With
Set DestinationRange = DestinationRange.Resize(SourceRange.Rows.Count, 4)
With DestinationRange
With .Offset(0, .Columns.Count + 1)
.Value = SourceRange.Value
strForm0 = "=SUMIF(" & SourceRange.Columns(1).Address(True, True, xlR1C1, True) & ", RC[-"
For i = 2 To 4
.Cells(1, i).Value = "Sum " & .Cells(1, i).Value
With .Offset(1, 0).Resize(.Rows.Count - 1, 4)
.Columns(i).FormulaR1C1 = strForm0 & (i - 1) & "], " & SourceRange.Columns(i).Address(True, True, xlR1C1, True) & ")"
End With
Next i
.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=DestinationRange.Rows(1), Unique:=True
.ClearContents
End With
End With
End Sub