On Sunday, February 3, 2013 11:41:15 PM UTC-8, joeu2004 wrote:
wrote:

Now I notice that while the "value if true" part works

the "value if false" does not, per below, using Enter

and/or Array-Enter.



Why would you array-enter the formula below? It does not have any array or

range parameters.



(But the formula that you did offer does need to be array-entered --

although it is nonsensical exactly you wrote it.)





wrote:

Enter

[....]

=SUM(IF(A1="X",B1+C1,"xx")) A1=Z #VALUE



Read the SUM help page carefully.



SUM ignores text in a parameter only if "an argument is an array or

reference".



In contrast, "if any arguments are text that cannot be translated into

numbers, Excel displays an error".



For example, SUM("xx") return an Excel error.



The SUM expression could be written as follows (and normally-entered):



=SUM(IF(A1="X",B1+C1,0))



Arguably, SUM is not needed at all in this context. It is sufficient to

write:



=IF(A1="X",B1+C1,0)



However, I believe your example is intended to be a distillation of an

array-entered formula of the form:



=SUM(IF(A1:A100="X",B1:B100+C1:C100,0))



In that context, we do not need the value-if-false part; it returns FALSE by

default.



Moreover, in that context, we could replace 0 with "xx" since

IF(A1:A100="X",...) returns an array. But really, there is no reason to do

that.

Thanks, joeu.
If I can re-find that page I'll read it closer.
This little formula purely slipped between my mental cracks.

=IF(A1="X",B1+C1,0)

Regards,
Howard