Why does COUNTA include cells with formulas but no text?

[LeoKing]

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.

[dajare]

According to the official help, 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 can use 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

[ ... ]      square brackets group alternates
    a-z0-9   any alphanumeric characters
    \.       can include a literal dot (escaped)
+            repeated any number of times

So if these rows represented A1:A5

""

Abc
3.1412
x

That COUNTIF returns a value of 3.

---

You could also see if there is inspiration in this StackOverflow Q&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.

(Update: regex is tricksy!)

[LeoKing]

Weird; 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?

[Regina]

When 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.

[dajare]

@LeoKing - I don’t suppose you could make your file available (Dropbox? Google Drive?), if the data wasn’t sensitive?

[Regina]

Or 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

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

[dajare]

When 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!

[dajare]

Still puzzling away at this. In this example, shouldn’t the result be 4? (D4 is empty)

[Regina]

@dajare - 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.

[dajare]

@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 if you like.

[dajare]

@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!

[Regina]

@dajare -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.

[Lupp]

Shouldn’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?

[LeoKing]

I 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.

[dajare]

@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!