Counting the number of "#N/A" errors

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
Try the ISNA() function, and also ERROR.TYPE().

Your formula is looking for a text string "#N/A".
 
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
 
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.
 
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!
 
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
 
Back
Top