Ask Your Question
1

Why does COUNTA include cells with formulas but no text? [closed]

asked 2014-06-10 00:28:34 +0200

Leo King gravatar image

updated 2015-09-04 23:28:28 +0200

Alex Kemp gravatar image

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.

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2016-02-25 10:09:12.944106

2 Answers

Sort by » oldest newest most voted
1

answered 2014-06-10 10:42:55 +0200

Regina gravatar image

updated 2014-06-11 01:22:47 +0200

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

edit flag offensive delete link more

Comments

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!

David gravatar imageDavid ( 2014-06-10 13:40:44 +0200 )edit

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

David gravatar imageDavid ( 2014-06-10 14:00:18 +0200 )edit

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

Regina gravatar imageRegina ( 2014-06-10 14:12:46 +0200 )edit

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

David gravatar imageDavid ( 2014-06-10 14:21:23 +0200 )edit

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

David gravatar imageDavid ( 2014-06-10 15:06:25 +0200 )edit

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

Regina gravatar imageRegina ( 2014-06-10 16:09:41 +0200 )edit

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?

Lupp gravatar imageLupp ( 2014-06-10 16:42:02 +0200 )edit

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.

Leo King gravatar imageLeo King ( 2014-06-10 23:02:20 +0200 )edit

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

David gravatar imageDavid ( 2014-06-11 09:04:24 +0200 )edit
1

answered 2014-06-10 01:41:53 +0200

David gravatar image

updated 2014-06-10 14:42:57 +0200

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

edit flag offensive delete link more

Comments

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?

Leo King gravatar imageLeo King ( 2014-06-10 22:58:41 +0200 )edit

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.

Regina gravatar imageRegina ( 2014-06-10 23:39:46 +0200 )edit

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

David gravatar imageDavid ( 2014-06-11 00:04:35 +0200 )edit

Question Tools

1 follower

Stats

Asked: 2014-06-10 00:28:34 +0200

Seen: 8,714 times

Last updated: Jun 11 '14