Biff or anyone,

Is this the only way to count both text "true" and logical TRUE?

=COUNTIF(A1:A10,"*true")+COUNTIF(A1:A10,"true")

Thanks.

Epinn

No. You could use any of the counting techniques: Sumif, Sumproduct, Sum(IF,
Count(If.

But COUNTIF is the best choice. Also, Countif is the only function that gets
"confused" on the TEXT versus LOGICAL true.

I would change the logical Countif to:

COUNTIF(A1:A10,TRUE)

Just so that I wouldn't get confused and think "true" refers to text.

Biff

"Epinn" wrote in message
...
Biff or anyone,

Is this the only way to count both text "true" and logical TRUE?

=COUNTIF(A1:A10,"*true")+COUNTIF(A1:A10,"true")

Thanks.

Epinn

See this:

http://tinyurl.com/u3gh6

Biff

"Biff" wrote in message
...
No. You could use any of the counting techniques: Sumif, Sumproduct,
Sum(IF, Count(If.

But COUNTIF is the best choice. Also, Countif is the only function that
gets "confused" on the TEXT versus LOGICAL true.

I would change the logical Countif to:

COUNTIF(A1:A10,TRUE)

Just so that I wouldn't get confused and think "true" refers to text.

Biff

"Epinn" wrote in message
...
Biff or anyone,

Is this the only way to count both text "true" and logical TRUE?

=COUNTIF(A1:A10,"*true")+COUNTIF(A1:A10,"true")

Thanks.

Epinn

Ooops!

You could use any of the counting techniques: Sumif,

Obviously, Sumif is not a counting technique!

Biff

"Biff" wrote in message
...
No. You could use any of the counting techniques: Sumif, Sumproduct,
Sum(IF, Count(If.

But COUNTIF is the best choice. Also, Countif is the only function that
gets "confused" on the TEXT versus LOGICAL true.

I would change the logical Countif to:

COUNTIF(A1:A10,TRUE)

Just so that I wouldn't get confused and think "true" refers to text.

Biff

"Epinn" wrote in message
...
Biff or anyone,

Is this the only way to count both text "true" and logical TRUE?

=COUNTIF(A1:A10,"*true")+COUNTIF(A1:A10,"true")

Thanks.

Epinn

Biff,

Thanks for your response.

When I said "the only way" my emphasis wasn't on COUNTIF but on "*true" and TRUE. I was wondering if checking both was the only way. I was hoping that I could write out "true" in some way *once* and I could catch both logical and text. I guess not.

I didn't know that only COUNTIF requires the wildcard and SUMPRODUCT was okay without it. Very strange.

=SUMPRODUCT(--(A1:A6="true"))+SUMPRODUCT(--(A1:A6=TRUE))

Thanks for telling me about #N/A. I have no problem using COUNTIF for #N/A but I have a problem using SUMPRODUCT.

=SUMPRODUCT(--(B1:B6=#N/A)) returns #N/A
=SUMPRODUCT(--(B1:B6="#N/A")) returns #N/A

Unlike "true" or TRUE, neither one of the above two formulae for #N/A works. Will stick with COUNTIF.

Any suggestion? No guarantee required.

Epinn

"Epinn" wrote in message ...
Biff or anyone,

Is this the only way to count both text "true" and logical TRUE?

=COUNTIF(A1:A10,"*true")+COUNTIF(A1:A10,"true")

Thanks.

Epinn

I don't like

=COUNTIF(B1:B6,"#N/A")

I know it works, but it just doesn't feel right, #N/A is not text. I much
prefer

=SUMPRODUCT(--ISNA(B1:B6))


"Epinn" wrote in message
...
Biff,

Thanks for your response.

When I said "the only way" my emphasis wasn't on COUNTIF but on "*true" and
TRUE. I was wondering if checking both was the only way. I was hoping that
I could write out "true" in some way *once* and I could catch both logical
and text. I guess not.

I didn't know that only COUNTIF requires the wildcard and SUMPRODUCT was
okay without it. Very strange.

=SUMPRODUCT(--(A1:A6="true"))+SUMPRODUCT(--(A1:A6=TRUE))

Thanks for telling me about #N/A. I have no problem using COUNTIF for #N/A
but I have a problem using SUMPRODUCT.

=SUMPRODUCT(--(B1:B6=#N/A)) returns #N/A
=SUMPRODUCT(--(B1:B6="#N/A")) returns #N/A

Unlike "true" or TRUE, neither one of the above two formulae for #N/A works.
Will stick with COUNTIF.

Any suggestion? No guarantee required.

Epinn

"Epinn" wrote in message
...
Biff or anyone,

Is this the only way to count both text "true" and logical TRUE?

=COUNTIF(A1:A10,"*true")+COUNTIF(A1:A10,"true")

Thanks.

Epinn

I agree with Bob.

=SUMPRODUCT(--(ISNA(B1:B6))

Biff

"Bob Phillips" wrote in message
...
I don't like

=COUNTIF(B1:B6,"#N/A")

I know it works, but it just doesn't feel right, #N/A is not text. I much
prefer

=SUMPRODUCT(--ISNA(B1:B6))


"Epinn" wrote in message
...
Biff,

Thanks for your response.

When I said "the only way" my emphasis wasn't on COUNTIF but on "*true"
and
TRUE. I was wondering if checking both was the only way. I was hoping
that
I could write out "true" in some way *once* and I could catch both logical
and text. I guess not.

I didn't know that only COUNTIF requires the wildcard and SUMPRODUCT was
okay without it. Very strange.

=SUMPRODUCT(--(A1:A6="true"))+SUMPRODUCT(--(A1:A6=TRUE))

Thanks for telling me about #N/A. I have no problem using COUNTIF for
#N/A
but I have a problem using SUMPRODUCT.

=SUMPRODUCT(--(B1:B6=#N/A)) returns #N/A
=SUMPRODUCT(--(B1:B6="#N/A")) returns #N/A

Unlike "true" or TRUE, neither one of the above two formulae for #N/A
works.
Will stick with COUNTIF.

Any suggestion? No guarantee required.

Epinn

"Epinn" wrote in message
...
Biff or anyone,

Is this the only way to count both text "true" and logical TRUE?

=COUNTIF(A1:A10,"*true")+COUNTIF(A1:A10,"true")

Thanks.

Epinn

Yes, I agree with both of you.

As a matter of fact, ISNA is probably *the* way to capture #N/A error because COUNTIF......#N/A purely won't work and not because it doesn't look right.

=COUNTIF(B1:B6,"#N/A") ...... I know it works ...... <<<

If I am not mistaken, COUNTIF ......"#N/A" or "#N/A*" only WORKS in capturing *text* "#N/A" and not the error #N/A. Unlike COUNTIF ...... TRUE, there is no way to use COUNTIF ...... #N/A (without quotes like TRUE) to count the error. So, looks like ISNA is the way.

If anyone wants to do things the hard way, may be one can try

COUNTIF ...... ISERROR minus COUNTIF ...... ISERR to count #N/A errors.

I say this because I have finally got the difference between ISERROR and ISERR registered in my memory bank.

Have a good weekend, guys.

Epinn

"Bob Phillips" wrote in message ...
I don't like

=COUNTIF(B1:B6,"#N/A")

I know it works, but it just doesn't feel right, #N/A is not text. I much
prefer

=SUMPRODUCT(--ISNA(B1:B6))


"Epinn" wrote in message
...
Biff,

Thanks for your response.

When I said "the only way" my emphasis wasn't on COUNTIF but on "*true" and
TRUE. I was wondering if checking both was the only way. I was hoping that
I could write out "true" in some way *once* and I could catch both logical
and text. I guess not.

I didn't know that only COUNTIF requires the wildcard and SUMPRODUCT was
okay without it. Very strange.

=SUMPRODUCT(--(A1:A6="true"))+SUMPRODUCT(--(A1:A6=TRUE))

Thanks for telling me about #N/A. I have no problem using COUNTIF for #N/A
but I have a problem using SUMPRODUCT.

=SUMPRODUCT(--(B1:B6=#N/A)) returns #N/A
=SUMPRODUCT(--(B1:B6="#N/A")) returns #N/A

Unlike "true" or TRUE, neither one of the above two formulae for #N/A works.
Will stick with COUNTIF.

Any suggestion? No guarantee required.

Epinn

"Epinn" wrote in message
...
Biff or anyone,

Is this the only way to count both text "true" and logical TRUE?

=COUNTIF(A1:A10,"*true")+COUNTIF(A1:A10,"true")

Thanks.

Epinn

"Epinn" wrote in message
...
Yes, I agree with both of you.

As a matter of fact, ISNA is probably *the* way to capture #N/A error
because COUNTIF......#N/A purely won't work and not because it doesn't
look right.


Not that it doesn't look right, it shouldn't work because #N/A is not text.
I think it only works because of poor coding, it wasn't designed to do so (a
personal view)


=COUNTIF(B1:B6,"#N/A") ...... I know it works ...... <<<

If I am not mistaken, COUNTIF ......"#N/A" or "#N/A*" only WORKS in
capturing *text* "#N/A" and not the error #N/A. Unlike
COUNTIF ...... TRUE, there is no way to use COUNTIF ...... #N/A
(without quotes like TRUE) to count the error. So, looks like ISNA is the
way.


No, you are wrong there, it counts true #N/As, either a simple =NA() or as a
result of another more complex conditional function.

Bob Phillips wrote...
....
Not that it doesn't look right, it shouldn't work because #N/A is not text.
I think it only works because of poor coding, it wasn't designed to do so (a
personal view)

The 2nd argument to COUNTIF/SUMIF *was* meant to be a criteria
expression. Whether that was meant to encompass error values is a
mystery Microsoft is unlikely to resolve, but it would have taken extra
work to ensure that something like "<#N/A" was treated as not equal to
the #N/A error value, and that argues for intent.

No, you are wrong there, it counts true #N/As, either a simple =NA() or as a
result of another more complex conditional function.

You're forgetting #N/A constants, but to pick the text "#N/A" it's
necessary to use

=COUNTIF(rng,"#N/A*")-COUNTIF(rng,"#N/A?*")

but

=SUMPRODUCT(--(rng="#N/A"))

would be best.