COUNTBLANK function when column lenght grows

[Dave]

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?

 

[T. Valko]

Use a dynamic range. See this:

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

 

[Mike H]

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

 

[Gord Dibben]

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

 

[Dave]

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.

 

[T. Valko]

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".

 

[Ashish Mathur]

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

 

[T. Valko]

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.