"Bob Phillips" wrote in message
...
"Ian P" wrote in message
...
"Bob Phillips" wrote:

"Ian P" wrote in message
...
Has anybody encountered a problem with the countblank function? I am
using a
formula =if(a21="","",a21); =if(a22="","",a22) etc. I then use the
countblank function to count the number of blanks in this array. If
I
delete
a21 then the formula delivers the blank cell but the countblank
doesn't
increase. Weirdly, if I press delete a second time then the
countblank
updates. I've checked the calculation options and everything seems
to
be
set
right so why would this happen?

Thanks

Ian

PS I know that it's easy to get round this by using other counts but
I
wondered why the countblank function doesn't work properly.

Are you sure that you are not clearing A21 with the delete key but
hitting
the spacebar? Space looks blank, but is not.

I've tried it in other worksheets (making sure that I use the delete key)
and
I get the same result every time. If I put values in cells A1:A6 and
then
use the formula =a1 in cell a18 and =a2 in cell a19 etc and then use the
=countblank(a18:a23) I get the result 0. If I then delete A1 I still get
zero. Press delete again and I get the result 1. Could someone else
with
Excel 2000 confirm if this does the same for them?

Aaah, now we understand. Countblank won't work because the cells are not
blank, they have a formula. The cells that they refer to may be blank, but
they are not.

Try
=COUNTIF(A18:A23,"")
instead

How would that explain the second delete in A1 affecting the COUNTBLANK?
[But the second delete doesn't affect the COUNTBLANK for me, with Excel
2003, so I don't know why it does for Ian with 2000.]
--
David Biddulph