COUNTBLANK function

[Ian P]

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.

 

[Bob Phillips]

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

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

 

[Ian P]

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?

Thanks

Ian

 

[Bob Phillips]

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


--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

 

[David Biddulph]

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

 

[Ian P]

The thing I don't understand (at risk of boring everyone with this) is why
when I delete A1, countblank remains 0 but when I press delete a second time
(i.e. press delete in the empty cell) then the formula suddenly kicks in and
the function counts my cell (with a formula in it ) as a blank. I can then
put a value back in A1 and the count goes back to zero. Press delete again
this time it works properly and I get a 1. I can then toggle between a value
and a blank cell and the formula works every time. I know there's a way
around this but I just wonder if this is a known fault.

Ian

 

[Kevin Vaughn]

I am using 2000 and I just tested this. It does not happen the same way for
me as it does for you. When I use countblank on a range that has formulas
that return "" if the cell they refer to is blank, then as soon as I delete
the cell that the above mentioned formulas refer to, the countblank increases
by 1. First I tried a countblank formula which was located on a row
underneath the range I was testing. I also tested it on a column/row which
was before the cells being tested and both times, the countblank updated as
soon as I hit delete.