variable array for count function

ivory

New Member
Joined
Dec 2, 2005
Messages
13
i want to count the number of blanks in a column, but the number of rows continually expands. how do i name a range so that it adjusts to the number of rows there currently is?

=countif(A1:A45,"") where A1:45 is the named range instead

Thanks in advance!
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,014
If I'm understanding you well, then the following should work.
<ul>[*]If text in column A: =MATCH(REPT("z",200),A:A)-COUNTA(A:A)[*]If numbers in column A: =MATCH(9E+299,A:A)-COUNT(A:A)[/list]Note that if your counting does not start on row 1 then you'd need to subtract the starting row.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,168
Messages
5,570,645
Members
412,335
Latest member
cinciri99
Top