Formula Explanation SUM(SUMIFS

xcelnovice

Board Regular
Joined
Dec 31, 2011
Messages
78
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!
 

Some videos you may like

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
Joined
Jul 14, 2014
Messages
1,678
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
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,154
=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

Board Regular
Joined
Dec 31, 2011
Messages
78
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
Joined
Oct 12, 2006
Messages
44,061
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
Joined
Jul 2, 2012
Messages
4,517
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
Joined
Jul 2, 2012
Messages
4,517
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
Joined
Dec 31, 2011
Messages
78
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
Joined
Oct 12, 2006
Messages
44,061
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,089,970
Messages
5,411,579
Members
403,380
Latest member
ifog671

This Week's Hot Topics

Top