sum If based on criteria (Excel 2003)

Topic

New Reply

I need a macro to Sum the Column H data to Group Totals at the bottom of Column H based on the Grouping number in Column C. The number of grouping is variable and the Groupings can be any number from 1 to 25

Reply | Quote

Replies

The formula =SUMIF(C:C,1,H:H) will return the total Profit Sharing for group 1.

Reply | Quote

Other options are Data | Subtotals and Data | PivotTable and PivotChart Report. See the two sheets in the attached version.

Reply | Quote

Great responses that do work. However I need this as a Macro . How do you code Sumif ?

Reply | Quote

You could use

Sub AddTotals()
Range(“H27”) = Application.SumIf(Range(“C2:C25”), 1, Range(“H2:H25”))
Range(“H28”) = Application.SumIf(Range(“C2:C25”), 2, Range(“H2:H25”))
End Sub

Reply | Quote

Try this one. The macro will add the groups and the sumif to the G and H columns two rows beneath your table. The only caveat is that you have to make sure there is nothing in the “G” cell two rows below your total line before you run the macro.

Dim i As Long, lRow As Long
lRow = Range("c1").End(xlDown).Row

Range("c1:c" & lRow - 1).AdvancedFilter Action:=xlFilterCopy, _
                        CopyToRange:=Range("G" & lRow + 2), Unique:=True
Range("g" & lRow + 2).Sort Key1:=Range("g" & lRow + 2), _
                        order1:=xlAscending, Header:=xlYes

Range("g" & lRow + 2).Delete shift:=xlUp

For i = Range("g" & lRow + 2).Row To Range("g" & lRow + 2).End(xlDown).Row
    Range("h" & i) = Application.SumIf(Range("C2:C" & lRow), _
    Range("g" & i), Range("H2:H" & lRow))
    Range("g" & i) = "Group " & Range("g" & i) & " Total ="
Next

Reply | Quote

Mike;
Thank you . It worked perfectly

Hans:
Thank you for the lesson

Reply | Quote