Counting the number of "#N/A" errors

B

Bob

The following formula finds the number of errors on the worksheet:
=SUMPRODUCT(--(ISERROR(A2:CY5000)))
=4

Now I'm trying to determine which errors were found, so I wrote the
following formula:
=COUNTIF(A2:CY5000,"#N/A")
=0

After manually looking through the entire worksheet I found 2 cells
containing "#N/A". Yet, my previous formula returned zero.

Is there a way I can find a specific error type (e.g., #N/A, #VALUE!, #REF!,
etc.)? If so, I would appreciate any help in coming up with the correct
formula.

Thanks,
Bob
 
D

David Biddulph

Try the ISNA() function, and also ERROR.TYPE().

Your formula is looking for a text string "#N/A".
 
M

Mujeeb ur Rehman, FBL-GRW-PK

Bob,

You have wrote the right formula. It must return u the right result. U can
check case sensetive like "#n/a" instead of "#N/A" without any space. I have
tried this function and that returned my your desired result: -

I repeat: =COUNTIF(A2:CY5000,"#N/A")

Mujeeb
 
B

Bob

David,
Thanks for the suggestion.
I tried the following formula:
=COUNTIF(A2:CY5000,ERROR.TYPE(A2:CY5000)=7)
But the result is still zero (it should be 2).
Am I doing something wrong?
Thanks again for your help,
Bob

P.S. I need to test for other error types besides #N/A. Hence, the reason
why using ISNA() is not an option here.
 
D

Domenic

Your COUNTIF formula should return the desired result. Maybe your
values are being recognized as text. Try coercing them into true error
values...

1) Select your range of cells

2) Edit > Replace

Find what: #N/A

Replace with: #N/A

3) Click Replace All

Hope this helps!
 
B

Biff

Ah, here's another one of Excel's anomalies!

Enter the TEXT value #N/A in some cells and try to use Countif. Do the same
thing with the TEXT values TRUE/FALSE.

To get it to work you must coerce the formula to explicitly evaluate the
criteria as TEXT. You can do this using the asterisk wildcard *:

=COUNTIF(A1:A5,"#N/A*")
=COUNTIF(A1:A5,"TRUE*")

Without the wildcard:

=COUNTIF(A1:A5,"TRUE")

Will count the LOGICAL value TRUE even though the criteria argument is
enclosed in quotes leading you to think it's evaluated as TEXT.

Biff
 
Top