Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Nov 2003
    Location
    Tampa, Florida, USA
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
    Attached Files Attached Files

  2. #2
    Lounger
    Join Date
    Sep 2008
    Posts
    49
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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.
    Attached Files Attached Files

  4. #4
    Star Lounger
    Join Date
    Nov 2003
    Location
    Tampa, Florida, USA
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: sum If based on criteria (Excel 2003)

    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

  6. #6
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    </pre>


  7. #7
    Star Lounger
    Join Date
    Nov 2003
    Location
    Tampa, Florida, USA
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
  •