Results 1 to 12 of 12

20120709, 18:55 #1
 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; 20120709 at 19:01.

20120710, 04:57 #2
 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

20120712, 07:32 #3
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,330
 Thanks
 3
 Thanked 218 Times in 201 Posts
You can do it with SUMPRODUCT
=SUMPRODUCT((criteria_range1=criteria1)*(criteria_range2=criteria2)*A1:B99)
Regards,
Rory
Microsoft MVP  Excel

20120712, 11:00 #4
 Join Date
 Jan 2004
 Location
 Praha
 Posts
 1,101
 Thanks
 75
 Thanked 127 Times in 111 Posts
Rory,
Doesn't that give an AND rather than the OR he is looking for ?

20120712, 11:03 #5
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,330
 Thanks
 3
 Thanked 218 Times in 201 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

20120712, 11:17 #6
 Join Date
 Jan 2004
 Location
 Praha
 Posts
 1,101
 Thanks
 75
 Thanked 127 Times in 111 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 ?

20120712, 11:35 #7
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,330
 Thanks
 3
 Thanked 218 Times in 201 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

20120712, 14:03 #8
 Join Date
 Jan 2004
 Location
 Praha
 Posts
 1,101
 Thanks
 75
 Thanked 127 Times in 111 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

20120712, 14:17 #9
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,330
 Thanks
 3
 Thanked 218 Times in 201 Posts
You did note that they are SUMIFS and not SUMIF formulas?
Regards,
Rory
Microsoft MVP  Excel

20120712, 17:35 #10
 Join Date
 Jan 2004
 Location
 Praha
 Posts
 1,101
 Thanks
 75
 Thanked 127 Times in 111 Posts
Yes indeed. Its the adding together of two the separate SUMIFS functions which creates the OR situation.

20120713, 02:03 #11
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,330
 Thanks
 3
 Thanked 218 Times in 201 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

20120713, 08:04 #12
 Join Date
 Jan 2004
 Location
 Praha
 Posts
 1,101
 Thanks
 75
 Thanked 127 Times in 111 Posts
Got it !
Thanks for being patient.
I learn such a lot here.
M