SUM for logical values

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?
--
tj

Try:

=A1+A2+A3
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"tjtjjtjt" <[email protected]> wrote in message
news:[email protected]...
> 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?
> --
> tj

Try

=SUMPRODUCT(--(A1:A3))

or array enter (ctrl + shift & enter)

=SUM(--(A1:A3))


--

Regards,

Peo Sjoblom

"tjtjjtjt" <[email protected]> wrote in message
news:[email protected]...
> 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?
> --
> tj

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.

--
tj


"Peo Sjoblom" wrote:

> Try
>
> =SUMPRODUCT(--(A1:A3))
>
> or array enter (ctrl + shift & enter)
>
> =SUM(--(A1:A3))
>
>
> --
>
> Regards,
>
> Peo Sjoblom
>
> "tjtjjtjt" <[email protected]> wrote in message
> news:[email protected]...
> > 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?
> > --
> > tj
>
>
>

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

tjtjjtjt wrote:

> 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?

this is what I have read in these newsgroups

suppose C1 to C8 are (logical values due to a question of two cells being
equal)

FALSE
FALSE
FALSE
FALSE
TRUE
TRUE
TRUE
TRUE
in an empty cell
=SUMPRODUCT(C1:C8*C1:C8)
gives 4


"Jerry W. Lewis" <post_a_reply@no_e-mail.com> wrote in message
news:4379D36C.5000607@no_e-mail.com...
> 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
>
> tjtjjtjt wrote:
>
> > 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?
>

I agree with you - I'm glad someone designed it. I'm still curious.

--
tj


"Jerry W. Lewis" wrote:

> 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
>
> tjtjjtjt wrote:
>
> > 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?
>
>