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.

Bob,

I tested before I post. I think I must have got COUNTIF and SUMPRODUCT mixed up.

COUNTIF ......#N/A with or without quotes counts the #N/A error. No argument there. Thank you for correcting me.

Harlan,

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

I understand the first part of the formula which is used to count text. I don't have a clue about the second part. Why the question mark? Please explain. How do I return/generate #N/A constants. Quite lost here.

=SUMPRODUCT(--(rng="#N/A"))
would be best. <<

As I stated in my previous (second) post under this thread, SUMPRODUCT doesn't work with #N/A.

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

I retested and it still didn't work.

I am going to use ISNA from now on.

Epinn

"Harlan Grove" wrote in message oups.com...
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"))

Epinn wrote...
....
=COUNTIF(rng,"#N/A*")-COUNTIF(rng,"#N/A?*") <<

I understand the first part of the formula which is used to count text. I don't have a clue
about the second part. Why the question mark? Please explain. How do I
return/generate #N/A constants. Quite lost here.

Text strings like "#N/Afoobar" are possible if highly unlikely. The
first COUNTIF call above would include such strings in its count. The
second COUNTIF call counts all text strings beginning with "#N/A" and
followed by at least one character. If you want to count only the text
"#N/A", then then you need to use the formula above first to count all
text strings beginning with #N/A then subtract any that have additional
characters.

=SUMPRODUCT(--(rng="#N/A"))
....
As I stated in my previous (second) post under this thread, SUMPRODUCT doesn't work
with #N/A.

With the error value #N/A, no, but my formula was for counting TEXT
"#N/A". If you have a range that contains both the error value #N/A and
the text string "#N/A", then SUMPRODUCT doesn't work.

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

Agreed.

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

Only if there's an #N/A error value in B1:B6.