[Solved] Countif with NA() not working

[dbird]

Trying to count the number of "#N/A" errors in a column of a openoffice spreadsheet. Here's my exact statement:

=COUNTIF(N4:N500;NA()) This is returning the error #N/A.

I also tried

=COUNTIF(N4:N500; "=NA()") This returns 0.

There are exactly 2 #N/A errors in the range. Works perfectly in google sheets. What's going on?

Thanks,

Dave

[Villeroy]

=SUMPRODUCT(ISNA(N4:N500))

[dbird]

Works perfectly. Thank you. Got any idea why the countif does not work?

Thanks again,

Dave

[Villeroy]

Any calculation with errors returns errors. ISNA(cell) returns if the cell has an error (1) or not (0) but not the error itself.
The array formula =ISNA(range) [Ctrl+Shift+Enter] returns an array of these tests consisting of 1 or 0
The array formula =SUM(ISNA(range)) [Ctrl+Shift+Enter] returns the sum of this array summing up all 1. This is equivalent to the above SUMPRODUCT formula but SUMPRODUCT does not require array input [Ctrl+Shift+Enter].

[dbird]

OK. Cool. So, you are also saying that countif cannot be used to count the number of NAs at all inherently.

Thanks,

Dave

[Villeroy]

=COUNTIF(ISNA(A1:A37);1) [Ctrl+Shift+Enter]

[dbird]

Hm! So, the only use for NA() in openoffice is to produce a #N/A error, which then poisons any function it's used in. That's pretty limiting.

[Villeroy]

This is how programming languages work. A spreadsheet is a simplified programming language.

[robleyd]

There are times when you might want to return an error, based on what your needs are, where otherwise Calc might not throw an error.

[dbird]

All quite true.

Thanks everyone, for the illumination, and the answers.

Dave