Using the example below, I need a worksheet formula that will automatically
determine the number of blank cells in column B based on the number of rows
in Col A containing entries, which can vary as more entries are added after
the last row. So the formula needs to automatically determine that number of
rows in column A for the whole column, not just a select number of rows. In
this example the number of rows is 5. Fortunately, the first null entry in
Col A will indicate the end of the data in that column. So there are no
nulls in Col A before the last entry. In my example, the answer is 3 (for
nulls found in Col B rows 2, 3 and 5). Any ideas? Thanks in advance.

Col A Col B
Row 1 10 X
Row 2 20
Row 3 25
Row 4 100 x
Row 5 15

=sumproduct(--(A1:A100<""),--(B1:B100=""))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Paputxi" wrote in message
...
Using the example below, I need a worksheet formula that will
automatically
determine the number of blank cells in column B based on the number of
rows
in Col A containing entries, which can vary as more entries are added
after
the last row. So the formula needs to automatically determine that number
of
rows in column A for the whole column, not just a select number of rows.
In
this example the number of rows is 5. Fortunately, the first null entry
in
Col A will indicate the end of the data in that column. So there are no
nulls in Col A before the last entry. In my example, the answer is 3 (for
nulls found in Col B rows 2, 3 and 5). Any ideas? Thanks in advance.

Col A Col B
Row 1 10 X
Row 2 20
Row 3 25
Row 4 100 x
Row 5 15

Perhaps try

=COUNTBLANK(B1:INDEX(B:B,COUNTA(A:A)))


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=527881

=SUMPRODUCT(--(A2:A10<""),--(B2:B10=""))

if you really want to determine the numbers of entries in A you would need a
dynamic formula like

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

however just use a a range that is a bit bigger than what can ever be there
the formula will still work


--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com




"Paputxi" wrote in message
...
Using the example below, I need a worksheet formula that will
automatically
determine the number of blank cells in column B based on the number of
rows
in Col A containing entries, which can vary as more entries are added
after
the last row. So the formula needs to automatically determine that number
of
rows in column A for the whole column, not just a select number of rows.
In
this example the number of rows is 5. Fortunately, the first null entry
in
Col A will indicate the end of the data in that column. So there are no
nulls in Col A before the last entry. In my example, the answer is 3 (for
nulls found in Col B rows 2, 3 and 5). Any ideas? Thanks in advance.

Col A Col B
Row 1 10 X
Row 2 20
Row 3 25
Row 4 100 x
Row 5 15