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" <(E-Mail Removed)> wrote in message
news:13756B6A-7DCA-41D3-ABA3-(E-Mail Removed)...
> 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 <(E-Mail Removed)>
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 <(E-Mail Removed)>
> 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" <(E-Mail Removed)> wrote in message
news72076CE-9F1E-478A-BD88-(E-Mail Removed)...
> 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 <(E-Mail Removed)>
>> 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" <(E-Mail Removed)> wrote in message
news:13756B6A-7DCA-41D3-ABA3-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
news:0C013B90-6991-447F-B3A4-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:13756B6A-7DCA-41D3-ABA3-(E-Mail Removed)...
>> 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?
>