SUM for logical values

T

tjtjjtjt

Using Excel 2002 SP3...

If I type logical values into the formula, like this: =SUM(TRUE, TRUE,
TRUE), my formula returns the value 3.
If I reference cells containing logical values, like this =SUM(A1:A3), my
formula returns 0.
Does anybody know why there is a difference?
 
P

Peo Sjoblom

Try

=SUMPRODUCT(--(A1:A3))

or array enter (ctrl + shift & enter)

=SUM(--(A1:A3))
 
T

tjtjjtjt

Thanks, to both of you...

I understand the alternatives.I've been using something like this:
=COUNTIF(A1:A3,TRUE)

I guess I was asking if there is a programming rationale as to why Excel is
treating literal logical values differently than cell references when the
cells contain logical values.
 
J

Jerry W. Lewis

SUM does not coerce non-numeric data types in ranges, but does coerce
them in constants. In addition to boolean values, =SUM("1","2") returns
3 and =SUM("1","2","Fred") returns #VALUE while =SUM(A1:A3) returns 0
with the corresponding values in that range.

But that is more a "what" than a "why". Best I can do on why is that it
is a design decision. I would not have designed it that way, but then I
didn't design it. On the other hand, I am glad that someone did design
it, and living with some design decisions that I wouldn't have made is
the price of using it.

Jerry
 
Top