Results 1 to 11 of 11

Thread: SUMIF (XP)

  1. #1
    Star Lounger
    Join Date
    Jan 2001
    Posts
    56
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SUMIF (XP)

    My worksheet has in column C a departmental name (there are 7 departments) and 4 data columns H, I, J, K with different methods of payments.

    I want to to calculate the total payments by each department in column C combined for all methods of payment In columns H,I,J, and K). I have used SUMIF successfully for 1 column of data but can't get it to work for 4 columns.

    Would appreciate any suggestions.

  2. #2
    Star Lounger
    Join Date
    Feb 2003
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SUMIF (XP)

    =SUMPRODUCT((C2:C100="DEPT")*(H2:K100))

  3. #3
    Star Lounger
    Join Date
    Jan 2001
    Posts
    56
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SUMIF (XP)

    Sorry but don't understand the comment "DEPT". Is this the specific dept name or does it refer to a defined range? and are the " " required?

  4. #4
    Star Lounger
    Join Date
    Feb 2003
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SUMIF (XP)

    Dept is the name of the department you are trying to sum. If the department is text, yes the "" are required.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SUMIF (XP)

    DEPT should be replaced by one of the department names, and yes, it should be in quotes. You need such a formula for each department.

    Another possible method is to create a pivot table with department in the row area, and four items, corresponding to each of the payment methods, in the data area.

  6. #6
    Star Lounger
    Join Date
    Jan 2001
    Posts
    56
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SUMIF (XP)

    Thanks to both of you

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: SUMIF (XP)

    In addition ot the SUMPRODUCT formula for multiple criteria, a "simple" solution may be to just use a Pivot table. This will summarize all the data as desired and you won't have to list each of the particular departments. The pivot will automatically extract a unique list and it will be updated when the pivot is refreshed if new data is added.

    Steve

  8. #8
    New Lounger
    Join Date
    May 2005
    Location
    Cleveland
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SUMIF (XP)

    quick question - what's the advantage of using the sumproduct function rather than sumif?

  9. #9
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SUMIF (XP)

    SUMPRODUCT uses this formula:

    <pre>=SUMPRODUCT((C2:C100="DEPT")*(H2:K100))
    </pre>


    To do the same thing with SUMIF would require:

    <pre>=SUMIF((C2:C100="DEPT")*(H2:H100))+SUMIF((C2: C100="DEPT")*(I2:I100))+SUMIF((C2:C100="DEPT")*(J2 :J100))+SUMIF((C2:C100="DEPT")*(K2:K100))
    </pre>


    There are also many things that you can do with SUMPRODUCT that can not be done at all with SUMIF.
    Legare Coleman

  10. #10
    New Lounger
    Join Date
    May 2005
    Location
    Cleveland
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SUMIF (XP)

    thanks for the reply.

    that not necessarily the sumif formula i would use. mine would be

    SUMIF($C$2:$C$100,"DEPT",H$2:H$100)

    and i'd copy it for each column and/or dept i wanted to sum. i was under the impression each column would be summed individually, but i could be wrong.

  11. #11
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SUMIF (XP)

    That formula would not give the result that the original poster wanted. That formula will sum only column H. The question was to sum columns H, I, J, and K when C is equal to "DEPT".
    Legare Coleman

Posting Permissions

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