Numerical value of logicals
 

As I understand it, when logicals are used in a formula, TRUE=1 and FALSE=0.
If this is so, then why is Excel evaluating the formula below as 0 instead of
1?

=SUMPRODUCT({FALSE,TRUE,FALSE},{TRUE,TRUE,FALSE})

Numerical value of logicals
 

You need to coerce the Boolean into numeric with an arithmetic operation.
Here are two example; each gives result of 1.

1) =SUMPRODUCT(--{FALSE,TRUE,FALSE}, --{TRUE,TRUE,FALSE})
This uses the unary negation operator (-) twice
2) =SUMPRODUCT({FALSE,TRUE,FALSE}*{TRUE,TRUE,FALSE})
This uses multiplication which is what
=SUMPRODUCT({FALSE,TRUE,FALSE}, {TRUE,TRUE,FALSE}) is expected to do but
does not!
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"hmm" wrote in message
...
As I understand it, when logicals are used in a formula, TRUE=1 and
FALSE=0.
If this is so, then why is Excel evaluating the formula below as 0 instead
of
1?

=SUMPRODUCT({FALSE,TRUE,FALSE},{TRUE,TRUE,FALSE})

Numerical value of logicals
 

Hi,

Try this instead. The double dash signs coerce TRUE to 1 and FALSE to 0.

SUMPRODUCT(--{FALSE,TRUE,FALSE},--{TRUE,TRUE,FALSE})

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"hmm" wrote in message
...
As I understand it, when logicals are used in a formula, TRUE=1 and
FALSE=0.
If this is so, then why is Excel evaluating the formula below as 0 instead
of
1?

=SUMPRODUCT({FALSE,TRUE,FALSE},{TRUE,TRUE,FALSE})

Numerical value of logicals
 

You need to turn the trues & falses to numbers.

One way is a double unary minus:
=SUMPRODUCT(--{FALSE,TRUE,FALSE},--{TRUE,TRUE,FALSE})
http://www.mcgimpsey.com/excel/formulae/doubleneg.html
http://xldynamic.com/source/xld.SUMPRODUCT.html

Another way is multiplication.
=SUMPRODUCT({FALSE,TRUE,FALSE}*{TRUE,TRUE,FALSE})
--
David Biddulph

"hmm" wrote in message
...
As I understand it, when logicals are used in a formula, TRUE=1 and
FALSE=0.
If this is so, then why is Excel evaluating the formula below as 0 instead
of
1?

=SUMPRODUCT({FALSE,TRUE,FALSE},{TRUE,TRUE,FALSE})

Numerical value of logicals
 

Thanks to all of you. The solutions you presented worked!

"hmm" wrote:

As I understand it, when logicals are used in a formula, TRUE=1 and FALSE=0.
If this is so, then why is Excel evaluating the formula below as 0 instead of
1?

=SUMPRODUCT({FALSE,TRUE,FALSE},{TRUE,TRUE,FALSE})