Formula Explanation SUM(SUMIFS

[xcelnovice]

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!

 

[NeonRedSharpie]

Let's break it down piece by piece:

[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

 

[Aladin Akyurek]

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

 

[xcelnovice]

Thank you both.

 

[xcelnovice]

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")

 

[Jonmo1]

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

 

[XOR LX]

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

 

[XOR LX]

I'm unaware of any method to add a 3rd multi criteria set..

There isn't, because you've "run out of dimensions"! In those cases you need to switch to SUMPRODUCT.

Regards

 

[xcelnovice]

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

 

[Jonmo1]

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.