Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenWed, 11 Jun 2014 09:04:24 +0200Why does COUNTA include cells with formulas but no text?https://ask.libreoffice.org/en/question/35266/why-does-counta-include-cells-with-formulas-but-no-text/I'm trying to use COUNTA to count all non-blank cells. But for some reason it treats cells with formulas but no text as non-blank. For example, if you type in ="" to a cell and then in another cell type =COUNTA([cell ref]), it will return 1. This is not what I want. I want it to discount any cells where there is no text even if there is a formula inside, to treat it the same as a truly empty cell. Is there a way to make this count?
To reproduce, open a Calc sheet and type the following values into the first three cells.
A1: [empty cell]
A2: =""
A3: =COUNTA(A1:A2)
A3 returns 1 on my version. Intuitively, it should return 0 - both A1 and A2 contain no text.
I have version 4.2.2.1.
Tue, 10 Jun 2014 00:28:34 +0200https://ask.libreoffice.org/en/question/35266/why-does-counta-include-cells-with-formulas-but-no-text/Answer by Regina for <p>I'm trying to use COUNTA to count all non-blank cells. But for some reason it treats cells with formulas but no text as non-blank. For example, if you type in ="" to a cell and then in another cell type =COUNTA([cell ref]), it will return 1. This is not what I want. I want it to discount any cells where there is no text even if there is a formula inside, to treat it the same as a truly empty cell. Is there a way to make this count?</p>
<p>To reproduce, open a Calc sheet and type the following values into the first three cells.</p>
<pre><code>A1: [empty cell]
A2: =""
A3: =COUNTA(A1:A2)
</code></pre>
<p>A3 returns 1 on my version. Intuitively, it should return 0 - both A1 and A2 contain no text.</p>
<p>I have version 4.2.2.1.</p>
https://ask.libreoffice.org/en/question/35266/why-does-counta-include-cells-with-formulas-but-no-text/?answer=35273#post-id-35273Or you can use the formula `=COUNTIF(LEN(A1:A2);">0")`, entered as array formula using Ctrl-Shift-Enter. It will count texts too, but neither empty cells nor empty strings nor errors. Or `=COUNTIF(LEN(A1:A2);"<>0")`, which will count errors in addition.
[test cases](/upfiles/14024024356329433.ods)
Update: There are two solutions, which seem to work the same in different versions of LibreOffice and Apache OpenOffice. The first one `=COUNTIF(A1:A2;".+")` needs regular expression, see Davids answer for details. The second one `{=SUM(A1:A2<>"")}` is an array-formula. Enter it without the curly brackets and finish with Ctrl+Shift+Enter.
[new test cases including these formulas](/upfiles/14024424646282776.ods)
Tue, 10 Jun 2014 10:42:55 +0200https://ask.libreoffice.org/en/question/35266/why-does-counta-include-cells-with-formulas-but-no-text/?answer=35273#post-id-35273Comment by David for <p>Or you can use the formula <code>=COUNTIF(LEN(A1:A2);">0")</code>, entered as array formula using Ctrl-Shift-Enter. It will count texts too, but neither empty cells nor empty strings nor errors. Or <code>=COUNTIF(LEN(A1:A2);"<>0")</code>, which will count errors in addition.</p>
<p><a href="/upfiles/14024024356329433.ods">test cases</a></p>
<p>Update: There are two solutions, which seem to work the same in different versions of LibreOffice and Apache OpenOffice. The first one <code>=COUNTIF(A1:A2;".+")</code> needs regular expression, see Davids answer for details. The second one <code>{=SUM(A1:A2<>"")}</code> is an array-formula. Enter it without the curly brackets and finish with Ctrl+Shift+Enter.</p>
<p><a href="/upfiles/14024424646282776.ods">new test cases including these formulas</a></p>
https://ask.libreoffice.org/en/question/35266/why-does-counta-include-cells-with-formulas-but-no-text/?comment=35277#post-id-35277When I first tried this, I failed to notice the "Ctrl-Shift-Enter" bit. :) But when I do that, it returns both empty cells and those with `""` in them. (This is with the first version of the formula.) What am I doing wrong? This is 4.2.4.2. Thanks!Tue, 10 Jun 2014 13:40:44 +0200https://ask.libreoffice.org/en/question/35266/why-does-counta-include-cells-with-formulas-but-no-text/?comment=35277#post-id-35277Comment by Regina for <p>Or you can use the formula <code>=COUNTIF(LEN(A1:A2);">0")</code>, entered as array formula using Ctrl-Shift-Enter. It will count texts too, but neither empty cells nor empty strings nor errors. Or <code>=COUNTIF(LEN(A1:A2);"<>0")</code>, which will count errors in addition.</p>
<p><a href="/upfiles/14024024356329433.ods">test cases</a></p>
<p>Update: There are two solutions, which seem to work the same in different versions of LibreOffice and Apache OpenOffice. The first one <code>=COUNTIF(A1:A2;".+")</code> needs regular expression, see Davids answer for details. The second one <code>{=SUM(A1:A2<>"")}</code> is an array-formula. Enter it without the curly brackets and finish with Ctrl+Shift+Enter.</p>
<p><a href="/upfiles/14024424646282776.ods">new test cases including these formulas</a></p>
https://ask.libreoffice.org/en/question/35266/why-does-counta-include-cells-with-formulas-but-no-text/?comment=35282#post-id-35282@David - Please look, what kind of delimiter between formula parameters is set in your LO. I have set semicolon to have the same as in AOO.
The results are indeed odd. I get different values in LO4.2 and LO 4.4 and AOO4.1. I'll attach a file with same tests. I have currently no idea what behavior is correct in regard to ODF1.2.Tue, 10 Jun 2014 14:12:46 +0200https://ask.libreoffice.org/en/question/35266/why-does-counta-include-cells-with-formulas-but-no-text/?comment=35282#post-id-35282Comment by David for <p>Or you can use the formula <code>=COUNTIF(LEN(A1:A2);">0")</code>, entered as array formula using Ctrl-Shift-Enter. It will count texts too, but neither empty cells nor empty strings nor errors. Or <code>=COUNTIF(LEN(A1:A2);"<>0")</code>, which will count errors in addition.</p>
<p><a href="/upfiles/14024024356329433.ods">test cases</a></p>
<p>Update: There are two solutions, which seem to work the same in different versions of LibreOffice and Apache OpenOffice. The first one <code>=COUNTIF(A1:A2;".+")</code> needs regular expression, see Davids answer for details. The second one <code>{=SUM(A1:A2<>"")}</code> is an array-formula. Enter it without the curly brackets and finish with Ctrl+Shift+Enter.</p>
<p><a href="/upfiles/14024424646282776.ods">new test cases including these formulas</a></p>
https://ask.libreoffice.org/en/question/35266/why-does-counta-include-cells-with-formulas-but-no-text/?comment=35285#post-id-35285@Regina - your "test cases" file is really helpful! I tried my regex suggestion with it: result is "2", finding A4 and A7: it excludes error, blank, empty string ... but also any string not-alphanumeric (so excluding any string with other than alphanumeric-plus-dot) so "space" is also excluded; altering regex to `"[a-z0-9[:space:]\.]+"` gets count of 3, finding A3, A4, and A7. FWIW!Tue, 10 Jun 2014 15:06:25 +0200https://ask.libreoffice.org/en/question/35266/why-does-counta-include-cells-with-formulas-but-no-text/?comment=35285#post-id-35285Comment by Regina for <p>Or you can use the formula <code>=COUNTIF(LEN(A1:A2);">0")</code>, entered as array formula using Ctrl-Shift-Enter. It will count texts too, but neither empty cells nor empty strings nor errors. Or <code>=COUNTIF(LEN(A1:A2);"<>0")</code>, which will count errors in addition.</p>
<p><a href="/upfiles/14024024356329433.ods">test cases</a></p>
<p>Update: There are two solutions, which seem to work the same in different versions of LibreOffice and Apache OpenOffice. The first one <code>=COUNTIF(A1:A2;".+")</code> needs regular expression, see Davids answer for details. The second one <code>{=SUM(A1:A2<>"")}</code> is an array-formula. Enter it without the curly brackets and finish with Ctrl+Shift+Enter.</p>
<p><a href="/upfiles/14024424646282776.ods">new test cases including these formulas</a></p>
https://ask.libreoffice.org/en/question/35266/why-does-counta-include-cells-with-formulas-but-no-text/?comment=35286#post-id-35286@David -But still fails for a local, where comma and not dot is used as decimal separator, and for locals with / and not dots in dates. To catch blank cell and cells containing empty string the regular expression `".+"` seems correct to me. It finds all cells with at least one character. AOO and LO versions differ only in the question whether errors are count or not. Your idea of using regular expression is really nice.Tue, 10 Jun 2014 16:09:41 +0200https://ask.libreoffice.org/en/question/35266/why-does-counta-include-cells-with-formulas-but-no-text/?comment=35286#post-id-35286Comment by Lupp for <p>Or you can use the formula <code>=COUNTIF(LEN(A1:A2);">0")</code>, entered as array formula using Ctrl-Shift-Enter. It will count texts too, but neither empty cells nor empty strings nor errors. Or <code>=COUNTIF(LEN(A1:A2);"<>0")</code>, which will count errors in addition.</p>
<p><a href="/upfiles/14024024356329433.ods">test cases</a></p>
<p>Update: There are two solutions, which seem to work the same in different versions of LibreOffice and Apache OpenOffice. The first one <code>=COUNTIF(A1:A2;".+")</code> needs regular expression, see Davids answer for details. The second one <code>{=SUM(A1:A2<>"")}</code> is an array-formula. Enter it without the curly brackets and finish with Ctrl+Shift+Enter.</p>
<p><a href="/upfiles/14024424646282776.ods">new test cases including these formulas</a></p>
https://ask.libreoffice.org/en/question/35266/why-does-counta-include-cells-with-formulas-but-no-text/?comment=35287#post-id-35287Shouldn't we consider to no longer recommend solutions by 'array expr.' in connection with evaluating criteria? In my opinion Regina's "odd" results" as well as my own observations may well be showing problems tracing back to lack of clarity or ambiguities in the specifications. I got examples where non-integer values were counted as being ">" than themselves. Today I played a bit around this topic and got a crazy result with ISLOGICAL() inside COUNTIF(). If can privide examples. Interested?Tue, 10 Jun 2014 16:42:02 +0200https://ask.libreoffice.org/en/question/35266/why-does-counta-include-cells-with-formulas-but-no-text/?comment=35287#post-id-35287Comment by David for <p>Or you can use the formula <code>=COUNTIF(LEN(A1:A2);">0")</code>, entered as array formula using Ctrl-Shift-Enter. It will count texts too, but neither empty cells nor empty strings nor errors. Or <code>=COUNTIF(LEN(A1:A2);"<>0")</code>, which will count errors in addition.</p>
<p><a href="/upfiles/14024024356329433.ods">test cases</a></p>
<p>Update: There are two solutions, which seem to work the same in different versions of LibreOffice and Apache OpenOffice. The first one <code>=COUNTIF(A1:A2;".+")</code> needs regular expression, see Davids answer for details. The second one <code>{=SUM(A1:A2<>"")}</code> is an array-formula. Enter it without the curly brackets and finish with Ctrl+Shift+Enter.</p>
<p><a href="/upfiles/14024424646282776.ods">new test cases including these formulas</a></p>
https://ask.libreoffice.org/en/question/35266/why-does-counta-include-cells-with-formulas-but-no-text/?comment=35280#post-id-35280Still puzzling away at this. In [this example](http://i.imgur.com/MCR6vZb.png), shouldn't the result be 4? (D4 is empty)Tue, 10 Jun 2014 14:00:18 +0200https://ask.libreoffice.org/en/question/35266/why-does-counta-include-cells-with-formulas-but-no-text/?comment=35280#post-id-35280Comment by David for <p>Or you can use the formula <code>=COUNTIF(LEN(A1:A2);">0")</code>, entered as array formula using Ctrl-Shift-Enter. It will count texts too, but neither empty cells nor empty strings nor errors. Or <code>=COUNTIF(LEN(A1:A2);"<>0")</code>, which will count errors in addition.</p>
<p><a href="/upfiles/14024024356329433.ods">test cases</a></p>
<p>Update: There are two solutions, which seem to work the same in different versions of LibreOffice and Apache OpenOffice. The first one <code>=COUNTIF(A1:A2;".+")</code> needs regular expression, see Davids answer for details. The second one <code>{=SUM(A1:A2<>"")}</code> is an array-formula. Enter it without the curly brackets and finish with Ctrl+Shift+Enter.</p>
<p><a href="/upfiles/14024424646282776.ods">new test cases including these formulas</a></p>
https://ask.libreoffice.org/en/question/35266/why-does-counta-include-cells-with-formulas-but-no-text/?comment=35320#post-id-35320@Regina This is great: wish I could +1 again! Thanks for your second file of test cases (I added a space in the "dummy text"). I just discovered there is an undocumented (for LibO) `[:punct:]` Posix regex. If used in the A12 formula instead of `[:space:]` it produces a result of "3": it counts A5-A7, and ignores A2-A4 -- but it excludes ` ` (literal space). Also could have used `[:print;]+` which is practically equivalent of `.+`. Anyway - I learned a lot from this. Thanks for your input!Wed, 11 Jun 2014 09:04:24 +0200https://ask.libreoffice.org/en/question/35266/why-does-counta-include-cells-with-formulas-but-no-text/?comment=35320#post-id-35320Comment by Leo King for <p>Or you can use the formula <code>=COUNTIF(LEN(A1:A2);">0")</code>, entered as array formula using Ctrl-Shift-Enter. It will count texts too, but neither empty cells nor empty strings nor errors. Or <code>=COUNTIF(LEN(A1:A2);"<>0")</code>, which will count errors in addition.</p>
<p><a href="/upfiles/14024024356329433.ods">test cases</a></p>
<p>Update: There are two solutions, which seem to work the same in different versions of LibreOffice and Apache OpenOffice. The first one <code>=COUNTIF(A1:A2;".+")</code> needs regular expression, see Davids answer for details. The second one <code>{=SUM(A1:A2<>"")}</code> is an array-formula. Enter it without the curly brackets and finish with Ctrl+Shift+Enter.</p>
<p><a href="/upfiles/14024424646282776.ods">new test cases including these formulas</a></p>
https://ask.libreoffice.org/en/question/35266/why-does-counta-include-cells-with-formulas-but-no-text/?comment=35308#post-id-35308I tried both solutions, and the second one returns TRUE no matter what, even if all cells in the range are absolutely blank. The first one returns a #VALUE! error.Tue, 10 Jun 2014 23:02:20 +0200https://ask.libreoffice.org/en/question/35266/why-does-counta-include-cells-with-formulas-but-no-text/?comment=35308#post-id-35308Comment by David for <p>Or you can use the formula <code>=COUNTIF(LEN(A1:A2);">0")</code>, entered as array formula using Ctrl-Shift-Enter. It will count texts too, but neither empty cells nor empty strings nor errors. Or <code>=COUNTIF(LEN(A1:A2);"<>0")</code>, which will count errors in addition.</p>
<p><a href="/upfiles/14024024356329433.ods">test cases</a></p>
<p>Update: There are two solutions, which seem to work the same in different versions of LibreOffice and Apache OpenOffice. The first one <code>=COUNTIF(A1:A2;".+")</code> needs regular expression, see Davids answer for details. The second one <code>{=SUM(A1:A2<>"")}</code> is an array-formula. Enter it without the curly brackets and finish with Ctrl+Shift+Enter.</p>
<p><a href="/upfiles/14024424646282776.ods">new test cases including these formulas</a></p>
https://ask.libreoffice.org/en/question/35266/why-does-counta-include-cells-with-formulas-but-no-text/?comment=35283#post-id-35283@Regina - I'm on LibO 4.2.4.2 under Ubuntu 14.04 (at the moment). It won't *let* me use a semi-colon! =/ You can [look at my file](https://drive.google.com/file/d/0B0GYFakD--Qba05kSjRVSjZLODA/edit?usp=sharing) if you like.Tue, 10 Jun 2014 14:21:23 +0200https://ask.libreoffice.org/en/question/35266/why-does-counta-include-cells-with-formulas-but-no-text/?comment=35283#post-id-35283Answer by David for <p>I'm trying to use COUNTA to count all non-blank cells. But for some reason it treats cells with formulas but no text as non-blank. For example, if you type in ="" to a cell and then in another cell type =COUNTA([cell ref]), it will return 1. This is not what I want. I want it to discount any cells where there is no text even if there is a formula inside, to treat it the same as a truly empty cell. Is there a way to make this count?</p>
<p>To reproduce, open a Calc sheet and type the following values into the first three cells.</p>
<pre><code>A1: [empty cell]
A2: =""
A3: =COUNTA(A1:A2)
</code></pre>
<p>A3 returns 1 on my version. Intuitively, it should return 0 - both A1 and A2 contain no text.</p>
<p>I have version 4.2.2.1.</p>
https://ask.libreoffice.org/en/question/35266/why-does-counta-include-cells-with-formulas-but-no-text/?answer=35268#post-id-35268According to the [official help](https://help.libreoffice.org/Calc/Statistical_Functions_Part_One#COUNTA), COUNTA counts "text entries ..., even when they contain an empty string of length 0". So it is behaving "correctly".
Depending on the nature of your data, you could try this:
=COUNTIF(A1:A2, "[a-z0-9\.]+")
which returns "0" for your example, since [COUNTIF](https://help.libreoffice.org/Calc/Mathematical_Functions#COUNTIF) can use [regular expressions](https://help.libreoffice.org/Common/List_of_Regular_Expressions). I don't know if that example is bullet-proof, though. (And you might need to enable Regular Expressions in Formula: go to `Options > LibreOffice Calc > Calculate` for the check-box.)
*Explanation of regex*
<pre>
[ ... ] square brackets group alternates
a-z0-9 any alphanumeric characters
\. can include a literal dot (escaped)
+ repeated any number of times
</pre>
So if these rows represented A1:A5
<pre>
""
Abc
3.1412
x
</pre>
That `COUNTIF` returns a value of 3.
---
You could also see if there is inspiration in this [StackOverflow Q&A](http://stackoverflow.com/a/22468947/232251) about the same situation in Google Docs spreadsheet, with a different solution offered. Don't know if that will work in your case, but it might be worth a look.
(Update: regex is tricksy!)Tue, 10 Jun 2014 01:41:53 +0200https://ask.libreoffice.org/en/question/35266/why-does-counta-include-cells-with-formulas-but-no-text/?answer=35268#post-id-35268Comment by David for <p>According to the <a href="https://help.libreoffice.org/Calc/Statistical_Functions_Part_One#COUNTA">official help</a>, COUNTA counts "text entries ..., even when they contain an empty string of length 0". So it is behaving "correctly".</p>
<p>Depending on the nature of your data, you could try this:</p>
<pre><code>=COUNTIF(A1:A2, "[a-z0-9\.]+")
</code></pre>
<p>which returns "0" for your example, since <a href="https://help.libreoffice.org/Calc/Mathematical_Functions#COUNTIF">COUNTIF</a> can use <a href="https://help.libreoffice.org/Common/List_of_Regular_Expressions">regular expressions</a>. I don't know if that example is bullet-proof, though. (And you might need to enable Regular Expressions in Formula: go to <code>Options > LibreOffice Calc > Calculate</code> for the check-box.)</p>
<p><em>Explanation of regex</em></p>
<pre>[ ... ] square brackets group alternates
a-z0-9 any alphanumeric characters
\. can include a literal dot (escaped)
+ repeated any number of times
</pre>
<p>So if these rows represented A1:A5</p>
<pre>""
Abc
3.1412
x
</pre>
<p>That <code>COUNTIF</code> returns a value of 3.</p>
<hr/>
<p>You could also see if there is inspiration in this <a href="http://stackoverflow.com/a/22468947/232251">StackOverflow Q&A</a> about the same situation in Google Docs spreadsheet, with a different solution offered. Don't know if that will work in your case, but it might be worth a look.</p>
<p>(Update: regex is tricksy!)</p>
https://ask.libreoffice.org/en/question/35266/why-does-counta-include-cells-with-formulas-but-no-text/?comment=35310#post-id-35310@Leo King - I don't suppose you could make your file available (Dropbox? Google Drive?), if the data wasn't sensitive?Wed, 11 Jun 2014 00:04:35 +0200https://ask.libreoffice.org/en/question/35266/why-does-counta-include-cells-with-formulas-but-no-text/?comment=35310#post-id-35310Comment by Leo King for <p>According to the <a href="https://help.libreoffice.org/Calc/Statistical_Functions_Part_One#COUNTA">official help</a>, COUNTA counts "text entries ..., even when they contain an empty string of length 0". So it is behaving "correctly".</p>
<p>Depending on the nature of your data, you could try this:</p>
<pre><code>=COUNTIF(A1:A2, "[a-z0-9\.]+")
</code></pre>
<p>which returns "0" for your example, since <a href="https://help.libreoffice.org/Calc/Mathematical_Functions#COUNTIF">COUNTIF</a> can use <a href="https://help.libreoffice.org/Common/List_of_Regular_Expressions">regular expressions</a>. I don't know if that example is bullet-proof, though. (And you might need to enable Regular Expressions in Formula: go to <code>Options > LibreOffice Calc > Calculate</code> for the check-box.)</p>
<p><em>Explanation of regex</em></p>
<pre>[ ... ] square brackets group alternates
a-z0-9 any alphanumeric characters
\. can include a literal dot (escaped)
+ repeated any number of times
</pre>
<p>So if these rows represented A1:A5</p>
<pre>""
Abc
3.1412
x
</pre>
<p>That <code>COUNTIF</code> returns a value of 3.</p>
<hr/>
<p>You could also see if there is inspiration in this <a href="http://stackoverflow.com/a/22468947/232251">StackOverflow Q&A</a> about the same situation in Google Docs spreadsheet, with a different solution offered. Don't know if that will work in your case, but it might be worth a look.</p>
<p>(Update: regex is tricksy!)</p>
https://ask.libreoffice.org/en/question/35266/why-does-counta-include-cells-with-formulas-but-no-text/?comment=35307#post-id-35307Weird; I tried it, and it returns TRUE if there is at least one non-empty cell in the range, and FALSE otherwise. The formula is exactly the same except the cell range of course. Any suggestions?Tue, 10 Jun 2014 22:58:41 +0200https://ask.libreoffice.org/en/question/35266/why-does-counta-include-cells-with-formulas-but-no-text/?comment=35307#post-id-35307Comment by Regina for <p>According to the <a href="https://help.libreoffice.org/Calc/Statistical_Functions_Part_One#COUNTA">official help</a>, COUNTA counts "text entries ..., even when they contain an empty string of length 0". So it is behaving "correctly".</p>
<p>Depending on the nature of your data, you could try this:</p>
<pre><code>=COUNTIF(A1:A2, "[a-z0-9\.]+")
</code></pre>
<p>which returns "0" for your example, since <a href="https://help.libreoffice.org/Calc/Mathematical_Functions#COUNTIF">COUNTIF</a> can use <a href="https://help.libreoffice.org/Common/List_of_Regular_Expressions">regular expressions</a>. I don't know if that example is bullet-proof, though. (And you might need to enable Regular Expressions in Formula: go to <code>Options > LibreOffice Calc > Calculate</code> for the check-box.)</p>
<p><em>Explanation of regex</em></p>
<pre>[ ... ] square brackets group alternates
a-z0-9 any alphanumeric characters
\. can include a literal dot (escaped)
+ repeated any number of times
</pre>
<p>So if these rows represented A1:A5</p>
<pre>""
Abc
3.1412
x
</pre>
<p>That <code>COUNTIF</code> returns a value of 3.</p>
<hr/>
<p>You could also see if there is inspiration in this <a href="http://stackoverflow.com/a/22468947/232251">StackOverflow Q&A</a> about the same situation in Google Docs spreadsheet, with a different solution offered. Don't know if that will work in your case, but it might be worth a look.</p>
<p>(Update: regex is tricksy!)</p>
https://ask.libreoffice.org/en/question/35266/why-does-counta-include-cells-with-formulas-but-no-text/?comment=35309#post-id-35309When it returns TRUE or FALSE, the cell format has been set to boolean somehow during your trials. Erase the cell content, clear the cell from all direct formatting, and set cell style to default. Try the formulas again then.Tue, 10 Jun 2014 23:39:46 +0200https://ask.libreoffice.org/en/question/35266/why-does-counta-include-cells-with-formulas-but-no-text/?comment=35309#post-id-35309