SUMIF "value if false" not working

[lhkittle]

Hi Experts,

I just offered this as a solution to a post in public.excel.
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.

In a Help search it said the "value if false" was optional, but if you include it should it not work?

Where A1=X and B1= 1 and C1= 2.
If A1=Z then I get #VALUE instead of xx.


Enter
=SUM(IF(A1="X",B1+C1,"xx")) A1=X Correct

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

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

Thanks.

Regards,
Howard

 

[joeu2004]

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.)

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.

 

[lhkittle]

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.)

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