How to express $A:$A with formula
 

Hi all,

A simple question. COUNTA($A:$A) gives me the right number of non-blank
cells in Column A. But, COUNTA(COLUMN(A1)) always seems to give me 1...

I don't want to hard-code "$A:$A", so want to replace it with something more
flexible. What formula should I use to achieve "$A:$A"?

Cheers, thanks for your time.
---
Tetsuya Oguma, Singapore

It's COLUMNS (plural), not COLUMN. Look up the difference in Help.

On Wed, 15 Dec 2004 17:53:02 -0800, "Tetsuya Oguma" <Tetsuya
wrote:

Hi all,

A simple question. COUNTA($A:$A) gives me the right number of non-blank
cells in Column A. But, COUNTA(COLUMN(A1)) always seems to give me 1...

I don't want to hard-code "$A:$A", so want to replace it with something more
flexible. What formula should I use to achieve "$A:$A"?

Cheers, thanks for your time.
---
Tetsuya Oguma, Singapore

Disregard the previous message. I was thinking of the VBA Columns method.

As for worksheet functions, there ARE two, COLUMN and COLUMNS, but the latter
won't solve your problem. COLUMNS(A1) also returns 1, but COLUMN(B1) returns 2
while COLUMNS(B1) returns 1.

Why don't you want to hard-code the reference? AFAIK, the only other formulas
would just be longer versions of the same thing, i.e.

=COUNTA(OFFSET($A$1,0,0,65536,1))
=COUNTA(INDIRECT(B1)) where B1 contains $A:$A


On Wed, 15 Dec 2004 20:30:16 -0600, Myrna Larson
wrote:

It's COLUMNS (plural), not COLUMN. Look up the difference in Help.

On Wed, 15 Dec 2004 17:53:02 -0800, "Tetsuya Oguma" <Tetsuya
wrote:

Hi all,

A simple question. COUNTA($A:$A) gives me the right number of non-blank
cells in Column A. But, COUNTA(COLUMN(A1)) always seems to give me 1...

I don't want to hard-code "$A:$A", so want to replace it with something more
flexible. What formula should I use to achieve "$A:$A"?

Cheers, thanks for your time.
---
Tetsuya Oguma, Singapore