[Solved] Countif with NA() not working
[Solved] Countif with NA() not working
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
=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
Last edited by RoryOF on Sun May 21, 2017 1:33 pm, edited 2 times in total.
Reason: Added green tick [RoryOF, Moderator]
Reason: Added green tick [RoryOF, Moderator]
Windows 10 Pro, OpenOffice 4.0, LibreOffice 7
Re: Countif with NA() not working
=SUMPRODUCT(ISNA(N4:N500))
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Countif with NA() not working
Works perfectly. Thank you. Got any idea why the countif does not work?
Thanks again,
Dave
Thanks again,
Dave
Windows 10 Pro, OpenOffice 4.0, LibreOffice 7
Re: Countif with NA() not working
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].
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].
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Countif with NA() not working
OK. Cool. So, you are also saying that countif cannot be used to count the number of NAs at all inherently.
Thanks,
Dave
Thanks,
Dave
Windows 10 Pro, OpenOffice 4.0, LibreOffice 7
Re: Countif with NA() not working
=COUNTIF(ISNA(A1:A37);1) [Ctrl+Shift+Enter]
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Countif with NA() not working
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.
Windows 10 Pro, OpenOffice 4.0, LibreOffice 7
Re: Countif with NA() not working [Solved]
This is how programming languages work. A spreadsheet is a simplified programming language.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: [Solved] Countif with NA() not working
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.
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.2.2; SlackBuild for 24.2.2 by Eric Hameleers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.2.2; SlackBuild for 24.2.2 by Eric Hameleers
Re: [Solved] Countif with NA() not working
All quite true.
Thanks everyone, for the illumination, and the answers.
Dave
Thanks everyone, for the illumination, and the answers.
Dave
Windows 10 Pro, OpenOffice 4.0, LibreOffice 7