Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: Multiple sumifs?

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I need to get a total of all values in column C only if column A equals Department and Column B equal TeamA.

    A________________B_______________C
    Department______TeamA___________.25
    Department______TeamA__________1.0
    Division________TeamA__________2.0
    Department______TeamB__________3.0
    Department______TeamA___________.25

    I only want the total to include rows 1,2 and 5. The value would be 1.5

    I tried the SUMIF command, but that only includes one column of criteria. (That I know how to do).

    Is there an easy way to do this with a formula?

    Thanks.

    [ This Message was edited by: sholmb1 on 2002-04-09 21:17 ]

    [ This Message was edited by: sholmb1 on 2002-04-09 21:17 ]

    [ This Message was edited by: sholmb1 on 2002-04-09 21:20 ]

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    3,424
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default


    =SUMPRODUCT((A2:A6="Department")*(B2:B6="TeamA")*(C2:C6))

    or use Cell references for Criteria

    =SUMPRODUCT((A2:A6=G1)*(B2:B6=G2)*(C2:C6))

    Revise references as necessary.

    or set up as Database and use DSum.

    [ This Message was edited by: Dave Patton on 2002-04-09 21:24 ]

  3. #3
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Sholmb1:
    You could use the following formula:

    =SUMIF(A2:A6,"=Department",C2:C6)-SUMIF(B2:B6,"=TeamB",C2:C6)

    that results in 1.5
    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  4. #4
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-09 21:22, Dave Patton wrote:

    =SUMPRODUCT((A2:A6="Department")*(B2:B6="TeamA")*(C2:C6))

    or use Cell references for Criteria

    =SUMPRODUCT((A2:A6=G1)*(B2:B6=G2)*(C2:C6))

    Revise references as necessary.

    or set up as Database and use DSum.

    [ This Message was edited by: Dave Patton on 2002-04-09 21:24 ]
    Hi Dave:
    I like this better than what I posted above ... this is more comprehensive in applicability. T H A N K S !

    [ This Message was edited by: Yogi Anand on 2002-04-09 21:46 ]

  5. #5
    New Member
    Join Date
    Apr 2002
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks Yogi and Dave.

    I think the SUMIF forumla misses the point. If the value of 3 is changed in my example to say 34, The answer is not 1.5 The SUMIF solution would work if the values in column C were constant.

    I am having some luck with the SUMPRODUCT formula. It works when I enter it on the same worksheet, but I am going between workbooks and getting a #value error when I add in the last columns (C) criteria into the formula (It works with only having column A and B in the sumproduct formula although it returns a total for the number of matches between the two columns (In my example above it would be 3). I don't know how, when I add in column C criteria that it changes to 1.5.)

    If you have other suggestions, I am open to anything.

    Thanks again for all your help.

  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,645
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-04-10 10:42, sholmb1 wrote:
    Thanks Yogi and Dave.

    I think the SUMIF forumla misses the point. If the value of 3 is changed in my example to say 34, The answer is not 1.5 The SUMIF solution would work if the values in column C were constant.

    I am having some luck with the SUMPRODUCT formula. It works when I enter it on the same worksheet, but I am going between workbooks and getting a #value error when I add in the last columns (C) criteria into the formula (It works with only having column A and B in the sumproduct formula although it returns a total for the number of matches between the two columns (In my example above it would be 3). I don't know how, when I add in column C criteria that it changes to 1.5.)

    If you have other suggestions, I am open to anything.

    Thanks again for all your help.
    I couldn't follow what you're saying about the SUMPRODUCT formula.

    Changing values in the ranges where SUMPRODUCT looks at will not affect the formula at all.

    What do you mean by moving between workbooks? Are you trying to apply the formula say from within WB1 on data in WB2?


  7. #7
    New Member
    Join Date
    Apr 2002
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I was able to get it to work. My issue was with 1) my formula. With the SUMPRODUCT function, all ranges need to be the same. (SUMPRODUCT(A4:A10)*(B4:B10)*(C4:C10)). 2) non numeric data in numeric fields. I had spaces in my column C that would give back a #value error. Once I cleaned all this up, it works like a charm. Thanks for all your help.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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