IF clauses with SUMPRODUCT
In normal usage (like your first example) SUMPRODUCT
doesn't need to be "array entered" but in some circumstances (like in your second example where you use an IF function) it does need "array entry"......so if you confirm the formula with CTRL+SHIFT+ENTER it will give the required results.
Note: in the specific situation shown SUMIF
will be better
=SUMIF(A1:A10;"Some text";B1:B10)
Related videos on Youtube
Doktoro Reichard
Just a simple Windows user merged with a need to mess around with these sorts of things. Maybe to deepen a little. I'm a 5th year college student. I also do some programming on the side. Since college will start soon, my rate of appearance will diminish, so I'm giving my personal experience I gained while debunking countless Windows problems. It also means that most of my advice is going to SuperUser instead of StackOverflow. For the Arqade folks... I would be a great addition to the thinking force, however, an evil Dwarf has sold my Quad Deathblow X44 Goggles to a money-mongering Goblin. (Long story short, my graphics board finally broke, after 8 years of providing joy, and I'm now using a older graphics board, that has left me out of the recent years in gaming... that and the gaming bug quietly died down). 2013-09-05 - My first Python script specifically designed to help someone at Superuser was written in a afternoon of boredom and heat. You can check out the question here and the code here 2013-09-09 - So, at last, my classes resumed. Expect my appearance to drop slowly in time.
Updated on September 18, 2022Comments
-
Doktoro Reichard almost 2 years
I'm having trouble understanding the following:
This works:
=SUMPRODUCT(N(A1:A10="Some text");B1:B10)
This doesn't:
=SUMPRODUCT(IF(A1:A10="Some text";1;0);B1:B10)
The
N()
function converts theTRUE
andFALSE
values the comparison operator returns to their numerical values, which is what theIF
should be doing, but isn't. Tracing the calculation shows an error in the value returned byIF
.An example of my error and usage (the Office is in Portuguese, however):
-
Darius over 10 yearsDon't IF functions needs , instead of ; ? So it should be if(A1:A10="Some text",1,0) ?
-
Jerry over 10 yearsAre you sure the
IF
is returning an error? It's returning the required value for me. @Darius, depending on regional settings, Excel uses;
or,
. -
Doktoro Reichard over 10 yearsActually it's the
A1:A10
range giving the first#VALUE!
error, that then propagates to the rest of the formula. But barry's answer seems spot on, although lacking in explanation as to why this behavior happens. -
Jerry over 10 years@DoktoroReichard Could you put some values in your question that reproduced that error? I can't see to get what you're getting.
-
Doktoro Reichard almost 10 yearsThe only logical reason I found as to the
N
function working without array formulation is that it internally creates an array, which is recognized by theSUMPRODUCT
, which is somethingIF
cannot do. By the comments, it appears this is only relevant in Excel 2003. As far as reasons go, this is a very weak one (which is why I'm not posting it as an answer), but it explains the behavior.