Here is a start:

Range("A1:A6").AdvancedFilter Action:=xlFilterCopy,
CopyToRange:=Range("E1"), Unique:=True
Range("F2").Select
ActiveCell.FormulaR1C1 =
"=SUMIF(RC[-5]:R[4]C[-5],RC[-1],RC[-4]:R[4]C[-4])"
Range("F2").Select
Range("F2").Select
Selection.End(xlDown).Select
lastrow = ActiveCell.Row + 1
Range("F2").Select
Do Until ActiveCell.Row = lastrow
MsgBox ("Total: " & ActiveCell.Value)
ActiveCell.Offset(1, 0).Select
Loop