HOW 2 COUNT ERRORS?

[FARAZ QURESHI]

Any formula to count the number of #N/A type errors in cells A1:G1?

 

[Mike H]

Hi,

Try this

=SUM(0+ISNA(A1:G1))

This is an array formula which must be entered by pressing CTRL+Shift+Enter
and not just Enter. If you do it correctly then Excel will put curly brackets
around the formula {}. You can't type these yourself. If you edit the formula
you must enter it again with CTRL+Shift+Enter.

Mike

 

[Pete_UK]

Try this:

=COUNTIF(A1:G1,NA())

Hope this helps.

Pete

 

[Jerry W. Lewis]

Two related possibilities are
=SUMPRODUCT(ISNA(A1:G1)*1)
=SUMPRODUCT(--ISNA(A1:G1))
SUMPRODUCT usually does not require array entry. *1 or -- coerces the
ISNA() array from T/F to 1/0.

Jerry