Combining 2 SUMIFs

John Carney
John Carney used Ask the Experts™
on
I really should know this by now, but how do I put these two SUMIFS together? Hopefully without an array because I'll have 600 of them on my worksheet.  What I need to determine is the sum of all cells in the range "AllPLanes" for which its corresponding cell in ALLAC = BB6   AND   its corresponding cell in ALLOPEN = BI6  

BC6 Formula: =SUMIF(AllAC,BB6,AllPlanes)
BJ6 Formula: =SUMIF(AllOpen,BI6,AllPlanes)

Thanks,
John

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2010

Commented:
Hello John,

Assuming AllAC, AllOpen, and AllPlanes have the same size and shape...

=SUMPRODUCT((AllAC=BB6)*(AllOpen=BI6)*(AllPlanes))

This use of SUMPRODUCT() depends on the function's ability to process arrays, and
the fact that Excel treats Boolean TRUE and FALSE as 1 and 0, respectively.  The
best treatment I have ever seen for this usage of SUMPRODUCT() is at:

http://xldynamic.com/source/xld.SUMPRODUCT.html

In summary:

1) SUMPRODUCT() will process each member of the designated arrays in turn,
multiplying the corresponding values in each array.  As the name suggests, the function
then takes the sum of the individual products as its return value

2) In this example, at least one of the arrays returns a TRUE or FALSE Boolean value.
When this value is multiplied by a numeric value, or if you use the double unary (double
minus sign) operator, Excel treats the Boolean value as 1 or 0

BTW, Excel 2007 and 2010 have a new SUMIFS function that allows multiple criteria...

Regards,

Patrick
Most Valuable Expert 2011
Awarded 2010
Commented:
Hi,
in Excel 2003 you'll need to use SUMPRODUCT to achieve this without an array formula
=sumproduct(--(AllAC=BB6),--(AllOpen=BI6),AllPlanes)
hope that helps
Top Expert 2008

Commented:
God help me. Saurabh's ellipses disease is spreading!
CompTIA Security+

Learn the essential functions of CompTIA Security+, which establishes the core knowledge required of any cybersecurity role and leads professionals into intermediate-level cybersecurity jobs.

Top Expert 2015

Commented:
lol @ Kevin.... hahahahaha.. Man i can't stop laughing myself out.
Saurabh...
John CarneyReliability Business Tools Analyst II

Author

Commented:
Hi Patrick, thanks. I put the formula in AZ6 and I'm getting an #N/A error.  Does that make any sense?
Top Expert 2015

Commented:
John,
No,points for this, Sumproduct pre-reqiuste is that your all the ranges in sumproduct should be the same what i mean by that is lets say your first range is from row-1 till row-100, Then all the ranges in the formula should be from row-1 till row-100, Otherwise it will give you a #NA error and again i'm under a assumption that you dont have any value which is having #NA error in your dataset.
Saurabh.
Most Valuable Expert 2011
Awarded 2010

Commented:
#N/A means that the data is not found in the lookup table. Check that the content of you cells BB6 and BI6 is really the same as in the ranges you specify.
- leading or trailing blanks
- numbers stored as text
are the most common culprits. There are ways around it, but you may want to check if the value exists in the range at all, first.
John CarneyReliability Business Tools Analyst II

Author

Commented:
teylyn, I tried yours as well with the same result. Kevin, I would have tried yours except that I can't remember what ellipsis means at the moment.
Top Expert 2008

Commented:
I'm not answering any more of your questions. You post a question and then leave for a meal, coffee, trip to Mexico, whatever.

Saurabh, are you coming to California?
Top Expert 2008

Commented:
Oh, how about:

=BC6+BJ6

I'm a freaking genius!
Top Expert 2008

Commented:
OK, I know that won't work.

Seriously, what are your ranges set to?
Top Expert 2008

Commented:
>#N/A means that the data is not found in the lookup table. Check that the content of you cells BB6 and BI6 is really the same as in the ranges you specify.

You will get zero in that case.

You get an error if the ranges are not the same size or if there are error values in the source data.
Most Valuable Expert 2011
Awarded 2010

Commented:
saurabh said:
>> Then all the ranges in the formula should be from row-1 till row-100,
Not necessarily, the second range can well be from row101 to row200, they just need to have the same number of cells.
Most Valuable Expert 2011
Awarded 2010

Commented:
Kevin, you're right, of course, I was confusing it with the main reason for VLookup to return #N/A ...
Top Expert 2008

Commented:
And now you are using ellipses! Yikes!
Most Valuable Expert 2011
Awarded 2010

Commented:
That's because I'm a puntuation freak
Top Expert 2008

Commented:
Interesting. I wonder why Saurabh does it.
Most Valuable Expert 2011
Awarded 2010

Commented:
maybe because he's a freak? <duck>
Top Expert 2008

Commented:
I didn't say it! I think it has something to do with how his brain works. I'm thinking his brain is operating at 20 or 30 times the speed that his fingers work and the brain is sending all these thoughts to his fingers and his fingers just kind of start thrashing and the period key is somehow the most vulnerable when this happens. I can just see him - his eyes wide with excitement and bloodshot from a lack of sleep - one eye is kind of twitchy - and his fingers are whipping out incoherent junk that looks kind of like English but is more like some hybrid gobbledygook. The right hand's ring finger starts to vibrate as the hands are overloaded with information and that period key starts getting hammered.
Top Expert 2010

Commented:
Uh, back to the question...

>>Hi Patrick, thanks. I put the formula in AZ6 and I'm getting an #N/A error.  Does that make any sense?

As I noted in my first comment, all of those ranges have to be the same size and shape.  Are they, or are they
not?

Also, are any of the cells in those ranges showing a #N/A error?
John CarneyReliability Business Tools Analyst II

Author

Commented:
Well this is curious. I notice that if "AllPlanes" has a number or nothing in it, I get the #N/A error, but if it has one cell with text in it, then I get a #VALUE! error. Does this shed any light on the problem?
Thanks,
John
Top Expert 2008

Commented:
If you use Teylyn's version you should avoid the error.

Kevin
Most Valuable Expert 2011
Awarded 2010

Commented:
double unary has its advantages. Now for the size of the ranges ... all the same?
Top Expert 2008

Commented:
>double unary has its advantages. Now for the size of the ranges ... all the same?

Actually, it's more about how SUMPRODUCT handles the arrays. When you do the math yourself you create errors. When you allow SUMPRODUCT to work with the arrays it can see the individual errors and ignore them.

Which leads me to think that yours will fail as well, unless the double unary operators are removed:

=SUMPRODUCT(AllAC=BB6,AllOpen=BI6,AllPlanes)

Kevin
Most Valuable Expert 2011
Awarded 2010

Commented:
OK, empirical approach, result of actual testing.

=sumproduct(--(AllAC=BB6),--(AllOpen=BI6),AllPlanes)
works with ranges the same size (surprise)
works with text and with numbers in all ranges, i.e. text will be FALSE and coerced to 0
returns #VALUE when ranges are not identically sized

=SUMPRODUCT((AllAC=BB6)*(AllOpen=BI6)*(AllPlanes))
works with ranges the same size
works with text and with numbers, the multiplication will do the coercion
returns #N/A when ranges are not identically sized
returns #Value when ranges are not identically sized OR the range to be summed, i.e. AllPlanes, has text mixed with numbers.

=SUMPRODUCT(AllAC=BB6,AllOpen=BI6,AllPlanes)
returns 0 with ranges the same size, regardless of data type of content
returns #VALUE when ranges are not identically sized

Sumproduct with comma and double unary evaluates each array and then ends up with

=SUMPRODUCT({1,1,1,1,1},{1,0,1,0,1},{4,5,"text",7,8})
this evaluates to a number

Sumproduct with multiplication ends up with

=SUMPRODUCT({1,1,1,1,1}*{1,0,1,0,1}*RangeName)  and then
=SUMPRODUCT({1,0,1,0,1}*{4,5,"text",7,8})
which bombs to #VALUE!

tested in XL2010 beta.

Long story short, John: Your ranges are not the same size. Check them. Then use my formula and don't worry about whether the contents is text or number :-)

cheers

teylyn
Top Expert 2015

Commented:
Sorry Guys, I was busy playing a soldier front so wasnt able to answer...
Kevin Said--> Saurabh, are you coming to California?
Yes, I'm all set to fly on 9th..and then flyback on 28th..Will be going all over the country...
terylyn said-->
Not necessarily, the second range can well be from row101 to row200, they just need to have the same number of cells.
Hmm interesting i haven't tried it, let me try it and will get back to you about it.
Kevin said-->
I didn't say it! I think it has something to do with how his brain works. I'm thinking his brain is operating at 20 or 30 times the speed that his fingers work and the brain is sending all these thoughts to his fingers and his fingers just kind of start thrashing and the period key is somehow the most vulnerable when this happens. I can just see him - his eyes wide with excitement and bloodshot from a lack of sleep - one eye is kind of twitchy - and his fingers are whipping out incoherent junk that looks kind of like English but is more like some hybrid gobbledygook. The right hand's ring finger starts to vibrate as the hands are overloaded with information and that period key starts getting hammered.
Man you are a genius, you seriously did nice analysis over me. Man you seriously rock, I guess you just in a wrong line you should be nasa scientist or something more greater then that coz you are simply genius and awesome...Anyways i use them because there is a long story behind it so if you are interested to listen to it let me know...will narrate the same to you...
Top Expert 2008

Commented:
Ugh! I was not of sane mind when I posted that last post. What I was thinking was this:

   =SUMPRODUCT((AllAC=BB6)*(AllOpen=BI6),AllPlanes)

Booleans do not generate errors, they are just ignored by SUMPRODUCT when doing the math inside the function. Therefore the need to ALWAYS do a conversion of boolean to numeric with some sort of math or conversion function.

Nice analysis Teylyn!

Kevin
Top Expert 2008

Commented:
Long story? This should be good. Sounds like a load of hooha to me.
Most Valuable Expert 2011
Awarded 2010

Commented:
Kevin, you don't mince your words, do ya? :-) Glad that I'm used to taking quite a lot of flak from the Kiwis. Germans in down under do not always mix well. And then, of course, there's the elipsis thing....
Top Expert 2008

Commented:
Argh!

OK, you're new in town. The ellipses/ellipsis/ellipse thing. Saurabh has been using it incorrectly for years and I give him a ration of it pretty much daily now. I've even managed to sneak in some edits on his profile using my magical powers. Believe it or not I think he picked up the habit from me of all people before I learned the rules of the ellipse. What I find amusing and worthy of jesting is that he seems to have taken it's misuse to stratospheric levels. A subplot of this story is that John pointed out awhile ago that, in my commentary of Saurabh's abuse of our language (English is a second language to Hindi,) I refer to the ellipse as "ellipses" which is really the plural. The singular's alternative spelling is ellipsis. Or something like that.

The rule which Saurabh, and just about everyone else, conveniently ignores, is that the ellipse is only used for two purposes: to indicate omitted words in a quote or reference, and to indicate a pause in dialog. Neither of these forms are used with any frequency in this forum and thus just about every instance of the ellipse is incorrect. And it's all because of my improper use before I learned the error of my ways.

Kevin
Top Expert 2015

Commented:
After reading all this, I'm just speechless, No comments to post.
Top Expert 2008

Commented:
>After reading all this, I'm just speechless, No comments to post.

But still posting bad punctuation anyway ;-)
John CarneyReliability Business Tools Analyst II

Author

Commented:
Look what I've wrought! This is like the Computer Techies meet the Round Table at the Algonquin. Very entertaining.
Okay, I've copied my data and formulas to another workbook, eliminating everything else in the actual book. Yes the ranges are the same size. In this demo workbook,
AllAC = Sheet2!$F$6:$F$602
AllOpen= Sheet2!$F$6:$F$602
AllPlanes= Sheet2!$F$6:$F$602
And the numbers (in this case, dates) are not stored as text.  I even tried formatting the dates as numbers with no decimals, and get the same disappointing result.
Usually this sort of problem is the result of me overlooking something obvious that a third grader could see, but lease take a look at the attached workbook and help me figure out what I'm doing wrong. In the actual workbook the data in the ranges are derived by formula, if that would make a difference for some odd reason.
I also copied everything from http://xldynamic.com/source/xld.SUMPRODUCT.html, and I get the same results!
Thanks,
John

SUMPRODUCT-Curiosity.xls
Most Valuable Expert 2011
Awarded 2010

Commented:
Your AllPlanes range contains #N/A cells. That will kill the sumproduct
Most Valuable Expert 2011
Awarded 2010
Commented:
Sorry, it's the AllAC range that has the #N/A, not the AllPlanes.

If Sumproduct encounters any of these
#N/A
#Ref!
#Div/0

in one of its ranges, it will return an error. It will return the first error it finds, so if the range had a #Ref, you'd see #Ref, if the range had a #div/0, you'd see that.

Replace the #n/a with blanks and you should be sweet.
John CarneyReliability Business Tools Analyst II

Author

Commented:
Thanks, teylyn. See what I mean about the third grader thing. I really must apologize to you (and everybody else) for the wild goose chase, because your first answer is the one that works for me. I feel terrible and elated at the same time.
And the rest of you, please forgive me for not sharing the points, but I couldn't see where to begin to do that.
Thanks so much. To quote the tv show, I'll try to be "as smart as a third grader next time"  :-)
John
John CarneyReliability Business Tools Analyst II

Author

Commented:
Thanks, teylyn. If I run into any Kiwis I'll tell them to lay off.

- John
Most Valuable Expert 2011
Awarded 2010

Commented:
Thanks, John! I'll chalk this up in my calendar as "The day that I won points over the brass"

Kevin, Saurabh, Patrick, thanks for an entertaining discussion.

cheers

teylyn

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial