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

1. ## 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.

2. 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. You can do it with SUMPRODUCT
=SUMPRODUCT((criteria_range1=criteria1)*(criteria_range2=criteria2)*A1:B99)

4. Rory,

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

5. 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.

6. 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. 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?

8. 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. You did note that they are SUMIFS and not SUMIF formulas?

10. Yes indeed. Its the adding together of two the separate SUMIFS functions which creates the OR situation.

11. 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?

12. 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
•