COUNTBLANK function works Ok for cells A1:A10, but what if the number of
cells grow to A1:A100 or beyond? How can I make the fofmula adjust
automaticlly?

Use a dynamic range. See this:

http://contextures.com/xlNames01.html#Dynamic

--
Biff
Microsoft Excel MVP


"Dave" wrote in message
...
COUNTBLANK function works Ok for cells A1:A10, but what if the number of
cells grow to A1:A100 or beyond? How can I make the fofmula adjust
automaticlly?

Hi,

It can be as simple as

=COUNTBLANK(A:A)

or to count blanks within the used range

=COUNTBLANK(INDIRECT("A1:A" &
SUMPRODUCT(MAX((ROW(A1:A1000))*(A1:A1000<"")))))

adjust 1000 to the maximum likely value
Mike

"Dave" wrote:

COUNTBLANK function works Ok for cells A1:A10, but what if the number of
cells grow to A1:A100 or beyond? How can I make the fofmula adjust
automaticlly?

I would suggest a creating a Dynamic Range for column A

Then a formula of =COUNTBLANK(myname)

See Debra Dalgleish's site for naming Dynamic Ranges.

http://www.contextures.on.ca/xlNames01.html#Dynamic


Gord Dibben MS Excel MVP

On Wed, 25 Feb 2009 13:58:01 -0800, Dave
wrote:

COUNTBLANK function works Ok for cells A1:A10, but what if the number of
cells grow to A1:A100 or beyond? How can I make the fofmula adjust
automaticlly?

To Mike H: Works great, thanks.
The naming Dynamic Ranges suggestion will take a while for this ex-TV
repairman to play with. Thanks to all.

"Gord Dibben" wrote:

I would suggest a creating a Dynamic Range for column A

Then a formula of =COUNTBLANK(myname)

See Debra Dalgleish's site for naming Dynamic Ranges.

http://www.contextures.on.ca/xlNames01.html#Dynamic


Gord Dibben MS Excel MVP

On Wed, 25 Feb 2009 13:58:01 -0800, Dave
wrote:

COUNTBLANK function works Ok for cells A1:A10, but what if the number of
cells grow to A1:A100 or beyond? How can I make the fofmula adjust
automaticlly?

TV repairman

I just had an "experience" with a TV repairman.

Charged me $400 to "fix" my 1.5 yr old Sony and it lasted 3days!

Instead of calling him again I decided it was cheaper to just buy a new TV
and chalk one up to "live and learn".

--
Biff
Microsoft Excel MVP


"Dave" wrote in message
...
To Mike H: Works great, thanks.
The naming Dynamic Ranges suggestion will take a while for this ex-TV
repairman to play with. Thanks to all.

"Gord Dibben" wrote:

I would suggest a creating a Dynamic Range for column A

Then a formula of =COUNTBLANK(myname)

See Debra Dalgleish's site for naming Dynamic Ranges.

http://www.contextures.on.ca/xlNames01.html#Dynamic


Gord Dibben MS Excel MVP

On Wed, 25 Feb 2009 13:58:01 -0800, Dave
wrote:

COUNTBLANK function works Ok for cells A1:A10, but what if the number of
cells grow to A1:A100 or beyond? How can I make the fofmula adjust
automaticlly?

Hi,

First of all, please supply a header to the range A1:A10 (therefore the
range would now shift to A2:A11 with the header in A1). Highlight A1:A11
and press Ctrl+L to convert the range to list (or Table in Excel 2007).
Please check the box for my list has headers. Now use A1:A11 in the countif
formula.

Now whenever you append data to the existing range, it will automatically
keep expanding in the range.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Dave" wrote in message
...
COUNTBLANK function works Ok for cells A1:A10, but what if the number of
cells grow to A1:A100 or beyond? How can I make the fofmula adjust
automaticlly?

Highlight A1:A11 and press Ctrl+L to convert the range to list (or Table in
Excel 2007).

That only works if you're using Excel 2003 or newer.

--
Biff
Microsoft Excel MVP


"Ashish Mathur" wrote in message
...
Hi,

First of all, please supply a header to the range A1:A10 (therefore the
range would now shift to A2:A11 with the header in A1). Highlight A1:A11
and press Ctrl+L to convert the range to list (or Table in Excel 2007).
Please check the box for my list has headers. Now use A1:A11 in the
countif formula.

Now whenever you append data to the existing range, it will automatically
keep expanding in the range.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Dave" wrote in message
...
COUNTBLANK function works Ok for cells A1:A10, but what if the number of
cells grow to A1:A100 or beyond? How can I make the fofmula adjust
automaticlly?