Using Counting #N/A

[anandasim]

Hi All,

I would like to count a range of cells, some with the typical #N/A cell
error condition. ISNA() requires a parameter within the brackets. How
do I use ISNA or similar within COUNTIF()?

Thanks
Ananda

 

[Jerry W. Lewis]

=SUMPRODUCT(--ISNA(range))

will count the number of cells in range that have the #N/A value.

Jerry

 

[Jude the Obscure]

Just use =COUNTIF(range, #N/A)


Regards

Charlie

 

[anandasim]

Thanks Charlie. That was pretty spot on.
Ananda

 

[anandasim]

Hi Jerry,
That was an interesting use of SUMPRODUCT. How on earth does it work?
ISNA(range) would return FALSE, then how does the -- work?
Intrigued
Ananda