# Thread: sum If based on criteria (Excel 2003)

1. ## sum If based on criteria (Excel 2003)

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

2. ## Re: sum If based on criteria (Excel 2003)

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

3. ## Re: sum If based on criteria (Excel 2003)

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

4. ## Re: sum If based on criteria (Excel 2003)

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

5. ## Re: sum If based on criteria (Excel 2003)

You could use

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

6. ## Re: sum If based on criteria (Excel 2003)

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.

<pre>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), _

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

</pre>

7. ## Re: sum If based on criteria (Excel 2003)

Mike;
Thank you . It worked perfectly

Hans:
Thank you for the lesson

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•