# IF clauses with SUMPRODUCT

I'm having trouble understanding the following:

1. This works:

``````=SUMPRODUCT(N(A1:A10="Some text");B1:B10)
``````
2. 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 Jan 12 '14 at 13:14
• 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 Jan 12 '14 at 13:15
• Actually 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 Jan 12 '14 at 13:50
• @DoktoroReichard Could you put some values in your question that reproduced that error? I can't see to get what you're getting. – Jerry Jan 12 '14 at 16:27

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)`
• The 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 the `SUMPRODUCT`, which is something `IF` 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. – Doktoro Reichard Jul 4 '14 at 21:50