Ask Your Question

Revision history [back]

click to hide/show revision 1
initial version

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.

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.