How to express $A:$A with formula

[Guest]

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.

 

[Myrna Larson]

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

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

 

[Myrna Larson]

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