count errors

[stef]

XL 2002 SP3
Win XP HE SP1

Follow-up to: microsoft.public.excel

How do I count errors #N/A in column A that has both errors and proper
values in some of its rows?

I am trying a countif but struggling w/ the error part e.g. isna or
iserror, etc.

tx.

 

[Dave Peterson]

How about:

=COUNTIF(A:A,"#n/a")

XL 2002 SP3
Win XP HE SP1

Follow-up to: microsoft.public.excel

How do I count errors #N/A in column A that has both errors and proper
values in some of its rows?

I am trying a countif but struggling w/ the error part e.g. isna or
iserror, etc.

tx.

 

[stef]

sure i can do that but it's not optimal since the error "code" could be
different....

 

[Dave Peterson]

I was confused by your post. I thought you wanted to only count N/A errors:

=SUMPRODUCT(--ISERROR(A1:A100))

Extend the range, but don't use the whole column.

 

[Biff]

Try this:

=SUMPRODUCT(--(ISNA(A1:A100)))

That will only count #N/A errors. It won't count #Value! or #REF! or #NUM!
etc.

If you want to count ALL errors:

=SUMPRODUCT(--(ISERROR(A1:A100)))

Biff

 

[stef]

That's it. Thanks

Try this:

=SUMPRODUCT(--(ISNA(A1:A100)))

That will only count #N/A errors. It won't count #Value! or #REF! or #NUM!
etc.

If you want to count ALL errors:

=SUMPRODUCT(--(ISERROR(A1:A100)))

Biff

 

[stef]

yes that's it, as if Biff's post.
thanks!

I was confused by your post. I thought you wanted to only count N/A errors:

=SUMPRODUCT(--ISERROR(A1:A100))

Extend the range, but don't use the whole column.

 

[Guest]

=COUNTIF(A1:A100,#N/A)

 

[Biff]

You're welcome!

Biff

That's it. Thanks