Revision history [back]

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?

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.

 3 retagged Alex Kemp 10514 ●14 ●42 ●42 http://etmg.altervista...

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.