countif results

L

Lookin for a job

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
 
A

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
 
L

Lookin for a job

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
 
K

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
 
P

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)
 
K

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
 
L

Lookin for a job

=IF(A1>2,"True","False")
can be written

=A1>2
Click to expand...

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
 
L

Lookin for a job

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
 
L

Lookin for a job

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
 
A

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
 
J

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*")
 
J

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
 
L

Lookin for a job

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.
 
Top