How to sum {TRUE,FALSE,TRUE} to get 2 ?

[eliW]

Hi,
It is a periodical blackout, please help.
Eli

 

[Ian Mac]

On 2002-06-06 03:19, eliW wrote:
Hi,
It is a periodical blackout, please help.
Eli

how are receiving the TRUE,FALSE,TRUE?

as you've described:

=SUM(TRUE,FALSE,TRUE)

=2

 

[sen_edp]

hi Eli

BOOK1
	A	B	C	D
1	10	20	TRUE
2	20	10	FALSE
3	10	20	TRUE
4			2
5
...

remember ctrl-shift-enter when entering array formulas
hth

 

[eliW]

Ian,
In A1:A3 I have TRUE,FALSE,TRUE
When I put in A4 =SUM(A1:A3) I get 0
What's the catch?
Eli

 

[Aladin Akyurek]

In A1:A3 I have TRUE,FALSE,TRUE
When I put in A4 =SUM(A1:A3) I get 0
What's the catch?

Eli,

Coercion is needed, therefore try:

=SUMPRODUCT((A1:A3)+0)

although I'd prefer:

=COUNTIF(A1:A3,TRUE)

Aladin

 

[Aladin Akyurek]

=SUM(TRUE,FALSE,TRUE)

=2



Ian,

When this set of logical values are in a range, say, A1:A3,

=SUM(A1:A3)

fails. The Help file states:

SUM(number1,number2, ...)

Number1, number2, ... are 1 to 30 arguments for which you want the total value or sum.

[1] Numbers, logical values, and text representations of numbers that you type directly into the list of arguments are counted. See the first and second examples following.


[2] If an argument is an array or reference, only numbers in that array or reference are counted. Empty cells, logical values, text, or error values in the array or reference are ignored. See the third example following.

=SUM(TRUE,FALSE,TRUE) by [1] results in 2.

=SUM(A1:A3), where A1=TRUE,A2=FALSE, and A3=TRUE, by [2] results in 0.

Aladin

 

[Ian Mac]

On 2002-06-06 05:00, Aladin Akyurek wrote:


=SUM(TRUE,FALSE,TRUE)

=2



Ian,

When this set of logical values are in a range, say, A1:A3,

=SUM(A1:A3)

fails. The Help file states:

SUM(number1,number2, ...)

Number1, number2, ... are 1 to 30 arguments for which you want the total value or sum.

[1] Numbers, logical values, and text representations of numbers that you type directly into the list of arguments are counted. See the first and second examples following.


[2] If an argument is an array or reference, only numbers in that array or reference are counted. Empty cells, logical values, text, or error values in the array or reference are ignored. See the third example following.

=SUM(TRUE,FALSE,TRUE) by [1] results in 2.

=SUM(A1:A3), where A1=TRUE,A2=FALSE, and A3=TRUE, by [2] results in 0.

Aladin

Your quite right! BUT I thought I'd pose the question and was about submit:

=SUMPRODUCT((E7:E11)*1)

until I was called off elsewhere.

 

[Mark W.]

On 2002-06-06 04:27, eliW wrote:
Ian,
In A1:A3 I have TRUE,FALSE,TRUE
When I put in A4 =SUM(A1:A3) I get 0
What's the catch?
Eli

Use any of...

{=SUM(A1:A3+0)}
=A1+A2+A3
=SUM(PRODUCT(A1:A3,1))

Note: The 1st formula is an array formula which must be entered using the Control+Shift+Enter key combination. The outermost braces, { }, are not entered by you -- they're supplied by Excel in recognition of a properly entered array formula. For more on array formulas see the Excel Help topic for "About array formulas and how to enter them".
This message was edited by Mark W. on 2002-06-06 08:36