Counting the number of "#N/A" errors
 

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

Counting the number of "#N/A" errors
 

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

Your formula is looking for a text string "#N/A".
--
David Biddulph

"Bob" wrote in message
...
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

Counting the number of "#N/A" errors
 

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

"Bob" wrote:

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

Counting the number of "#N/A" errors
 

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.



"David Biddulph" wrote:

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

Your formula is looking for a text string "#N/A".
--
David Biddulph

"Bob" wrote in message
...
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

Counting the number of "#N/A" errors
 

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!

In article ,
Bob wrote:

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

Counting the number of "#N/A" errors
 

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

"Bob" wrote in message
...
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