countif results

[Guest]

Hello Guys..

I have a range of IF formulas in A10:A20 that return either a text "True"
(not a boolean) or a blank "" .

There are several "Trues" in the range but when I use -

=Countif(A10:A20,"True")

I get a result of 0.

Why is this?

Thanks

 

[Anne Troy]

"True" (in quotes) means literal text. The text isn't literal. Rather "true"
in this case is a value. So, you dont' need the quotes in your formula.

=Countif(A10:A20,TRUE)
*******************
~Anne Troy

www.OfficeArticles.com

 

[Guest]

Hello Anne -

That doesn't work as I had already tried that......still returns 0.

What it is about True that causes this problem? In the IF formula when I
change the text "True" to the boolean TRUE then use -

=Countif(A10:A20,TRUE)

I get the correct result.

I guess I can use the boolean value but would still like to know why True
doesn't get recognized ?????

Thanks

 

[kk]

Try this

=SUMPRODUCT(--(A10:A20="TRUE"))

Does it help?


message Hello Guys..

I have a range of IF formulas in A10:A20 that return either a text "True"
(not a boolean) or a blank "" .

There are several "Trues" in the range but when I use -

=Countif(A10:A20,"True")

I get a result of 0.

Why is this?

Thanks

 

[Peo Sjoblom]

Why would you even type in true in an if formula? E.g.

=IF(A1>2,"True","False")

can be written

=A1>2

instead, there is no need to write true or false

--
Regards,

Peo Sjoblom

(No private emails please)

 

[KL]

Hi,

Since you are pretty confident that True is text and not a logical value,
why don't you try something like this (just in case there is an extra space
o something):

=COUNTIF(A10:A20,"*True*")

Regards,
KL

 

[Guest]

=IF(A1>2,"True","False")

can be written

=A1>2

Yes it can but I don't want a FALSE return. I want "".

I can use the boolean TRUE -

=IF(A1>2,True,"")

But why doesn't "true" get recognized?

What's the difference between "apples" and "true" in a Countif?

Seems to me there is none ???

Thanks

 

[Guest]

That works and I'm absolutely certain that the IF formula uses the text value
"true" and not the boolen value TRUE and there are no extra chars in the
cells.

I really appreciate everyones input and suggestions but does anybody know
WHY -

=Countif(A10:20,"true")

Thanks

 

[Guest]

That certainly will work but the Countif is more efficient.

I changed the text true to the boolean TRUE.

I really appreciate everyones help but can anyone answer the question as to
why -

=Countif(A10:A20,"true")

returns 0 ???

Thanks

 

[Anne Troy]

Okay. I think you'd better give us the exact formulas in your A10 to A20
'cause I can't replicate the problem you're having. Works for me whether
"TRUE" or TRUE.

*******************
~Anne Troy

www.OfficeArticles.com

 

[JE McGimpsey]

Hmm... not for me (XL03/04).

Using
=COUNTIF(A10:A20,TRUE)

or

=COUNTIF(A10:A20,"TRUE")

fails to count cells in A10:A20 containing Text "TRUE".

OTOH this counted Text "TRUE" but not boolean TRUE:

=COUNTIF(A10:A20,"TRUE*")

 

[Guest]

Have a look see at this screencap -

http://img177.imageshack.us/img177/9371/countif4tc.jpg

It seems aparent to me that Countif doesn't like a text "True".

I'm just wanting to know why.

What's the difference between -

=Countif(A10:A20,"apples")

and

=Countif(A10:A20,"True")

Thanks

 

[KL]

Wow! That's weird. I can reproduce this in XL2K.

KL

 

[JE McGimpsey]

I'm certainly not an authority, but I'll take a guess. The
implementation of COUNTIF() parses the comparison argument as a string,
so

=COUNTIF(A10:A20,"=" & B1)

will interpret the concatenated string and attempt to deduce the
argument type from the context. When a comparison operator is left out,
"=" is implied, so just as

=COUNTIF(A10:A20,10)

is syntactically equivalent to

=COUNTIF(A10:A20,"=10")

So

=COUNTIF(A10:A20,"True")

is syntactically equivalent to

=COUNTIF(A10:A20,"=True")

Obviously, the function parser will preferentially interpret arguments
as numbers or booleans rather than strings, so the boolean will be
matched.

However, when you use a wildcard:

=COUNTIF(A10:A20,"TRUE*")

it forces the parser to interpret the argument as a string, and thus
match

 

[Guest]

That sounds reasonable.

Plenty of alternatives available to avoid this but it just seemed like such
a simple and basic formula that had me baffled.

I've been using XL for years and can't ever remember running into this before.

Thanks to everyone for their input.