Results 1 to 12 of 12
  1. #1
    New Lounger
    Join Date
    Jan 2010
    Location
    Sacramento, CA
    Posts
    7
    Thanks
    3
    Thanked 1 Time in 1 Post

    How to sum 2 sum_ranges using one SUMIFS? (Excel 2010)

    I'd like to sum A1:A99 and B1:B99 when certain criteria are met. Currently I'm using:

    =SUMIFS(A1:A99,criteria_range1,criteria1,criteria_ range2,criteria2) + SUMIFS(B1:B99,criteria_range1,criteria1,criteria_r ange2,criteria2)

    I can't find a way to do this in one SUMIFS function, as in:

    SUMIFS(A1:A99+B1:B99,criteria_range1,criteria1 ...etc)

    Is there a way? Thanks.
    Last edited by artg; 2012-07-09 at 20:01.

  2. #2
    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
    I don't think there is a way due to the nature of the SUMIFS. A sumifs is a series of criteria which are compared to an AND (they all must be true). The 2 conditions you want to compare are not being ANDed, but ORed.

    You could use something like SUMIFS (A1:B99, ...) but then the criteria ranges must also contain 2 columns which may not be exactly what you are after.

    What is "wrong" with adding 2 distinct formulas to get a value?
    Steve

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    You can do it with SUMPRODUCT
    =SUMPRODUCT((criteria_range1=criteria1)*(criteria_range2=criteria2)*A1:B99)

    Regards,
    Rory

    Microsoft MVP - Excel

  4. #4
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    Rory,

    Doesn't that give an AND rather than the OR he is looking for ?

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Where did OR appear in the question? All I could see was trying to do the same SUMIFS but with two data columns rather than one.
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    By ADDING two separate SUMIFs, as he shows in the first post, he is getting the sum if either meets the criterion - that is to say one OR the other. The SUMPRODUCT only adds to the total when both criteria are true, that is to say one AND the other.

    Am I missing something ?

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    The criteria are the same in each formula. All that changes is the data column. So if the criteria are met, sum both columns, no?
    Regards,
    Rory

    Microsoft MVP - Excel

  8. #8
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    Rory, I think we are at cross purposes, but no doubt the OP will test the solutions to make sure he is getting the desired result

  9. #9
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    You did note that they are SUMIFS and not SUMIF formulas?
    Regards,
    Rory

    Microsoft MVP - Excel

  10. #10
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    Yes indeed. Its the adding together of two the separate SUMIFS functions which creates the OR situation.

  11. #11
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    I am still not seeing the OR. Same criteria applied to same rows but different data columns. You either sum both columns for a given row, or neither.

    SUMIFS(A1:A10,C1:C10,"B",D1: D10,"something")+SUMIFS(B1:B10,C1:C10,"B",D1: D10,"something")

    Or are you assuming that the criteria are different in each SUMIFS formula?
    Regards,
    Rory

    Microsoft MVP - Excel

  12. #12
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    Got it !
    Thanks for being patient.
    I learn such a lot here.
    M

Posting Permissions

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