0
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 the TRUE
and FALSE
values the comparison operator returns to their numerical values, which is what the IF
should be doing, but isn't. Tracing the calculation shows an error in the value returned by IF
.
An example of my error and usage (the Office is in Portuguese, however):
Don't IF functions needs , instead of ; ? So it should be if(A1:A10="Some text",1,0) ? – Darius – 2014-01-12T13:14:29.753
Are you sure the
IF
is returning an error? It's returning the required value for me. @Darius, depending on regional settings, Excel uses;
or,
. – Jerry – 2014-01-12T13:15:15.053Actually 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. – Doktoro Reichard – 2014-01-12T13:50:41.707@DoktoroReichard Could you put some values in your question that reproduced that error? I can't see to get what you're getting. – Jerry – 2014-01-12T16:27:43.913