Formula Explanation SUM(SUMIFS

xcelnovice

Board Regular
Joined
Dec 31, 2011
Messages
81
Hi can someone help me understand this. Im just kind of looking for a text based explanations as to what is going on here...

=SUM(SUMIFS(Sheet1!J:J,Sheet1!D:D,"Mailroom",Sheet1!I:I,"OT",Sheet1!A:A,{"IMAG","INQC","MAIL"}))

Thanks in Advance!
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Let's break it down piece by piece:

Code:
[COLOR=#333333]SUMIFS(Sheet1!J:J,Sheet1!D:D,"Mailroom",Sheet1!I:I,"OT",Sheet1!A:A,{"IMAG","INQC","MAIL"})[/COLOR]
This is the meat of your formula, and you shouldn't be needing the SUM function at all. It should return the same without it. Now, a SUMIFS syntax is:

SUMIFS(Sum range, criteria range 1, criteria 1, criteria range 2, criteria 2, etc...)

So it's a SUM of all the values on Sheet1 column J that meet the criteria of:

- Corresponding row, column D is equal to Mailroom
- Corresponding row, column I is equal to OT
- Corresponding row, column A is equal to IMAG or INQC or MAIL
 
Upvote 0
=SUM(SUMIFS(Sheet1!J:J,Sheet1!D:D,"Mailroom",Sheet1!I:I,"OT",Sheet1!A:A,{"IMAG","INQC","MAIL"}))

is exactly equivalent to:

=SUMIFS(Sheet1!J:J,Sheet1!D:D,"Mailroom",Sheet1!I:I,"OT",Sheet1!A:A,"IMAG")+
SUMIFS(Sheet1!J:J,Sheet1!D:D,"Mailroom",Sheet1!I:I,"OT",Sheet1!A:A,"INQC")+
SUMIFS(Sheet1!J:J,Sheet1!D:D,"Mailroom",Sheet1!I:I,"OT",Sheet1!A:A,"MAIL")

Thus the formula with an array of criteria {"IMAG","INQC","MAIL"} forces SUMIFS to run for each criterion separately and arriving at a result like say...

{3,0,8}

Such fed to a surrounding SUM yields: 11.
 
Upvote 0
Hi,

So I can get the below formula to work individually, but not as an array. Can anyone add some insight on how I can make this an array formula.

=SUMIFS(DATA!$G:$G,DATA!$L:$L,"Cust Svc",DATA!$J:$J,"OH00",DATA!$F:$F,"REG")
+SUMIFS(DATA!$G:$G,DATA!$L:$L,"Cust Svc",DATA!$J:$J,"OVRH",DATA!$F:$F,"REG")
+SUMIFS(DATA!$G:$G,DATA!$L:$L,"Cust Svc",DATA!$J:$J,"OH00",DATA!$F:$F,"OT")
+SUMIFS(DATA!$G:$G,DATA!$L:$L,"Cust Svc",DATA!$J:$J,"OVRH",DATA!$F:$F,"OT")
 
Upvote 0
When you have 2 seperate sets of multi criteria...

J = A OR B
AND
F = C OR D

You have to transpose one of the sets of criteria.

Try
=SUM(SUMIFS(DATA!$G:$G,DATA!$L:$L,"Cust Svc",DATA!$J:$J,{"OH00","OVRH"},DATA!$F:$F,{"REG";"OT"}))

Notice the first set uses a comma, the second uses a semicolon.


I'm unaware of any method to add a 3rd multi criteria set..
 
Upvote 0
This is the meat of your formula, and you shouldn't be needing the SUM function at all. It should return the same without it.

On the contrary. Of course you need the initial SUM function as well when you have several OR criteria being referenced.

Regards
 
Upvote 0
Thank you that works, but can you briefly explain what the =SUM portion does, how it changes the formula. I hate black boxes, I need to understand
 
Upvote 0
Wasn't that explained (and you gave thanks for) in Post #3?

Putting multi criteria in a constant array like {"IMAG","INQC","MAIL"}
Foces sumif to basically do 3 sumifs, one for IMAG, one for INQC and one for MAIL
Then the SUM, sums the results of those 3 results.
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,986
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top