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