# Formula Explanation SUM(SUMIFS

#### xcelnovice

##### Board Regular
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"}))

### Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

#### NeonRedSharpie

##### Well-known Member
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

##### MrExcel MVP
=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.

Thank you both.

#### xcelnovice

##### Board Regular
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

##### MrExcel MVP
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

##### Well-known Member
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

##### Well-known Member
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

##### Board Regular
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

##### MrExcel MVP
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.