COUNTBLANK function

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.

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

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

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" wrote:

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

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)

"Ian P" > wrote in message
...
> 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" wrote:
>
> > 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" > 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.
> >
> >
> >

"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

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

"Bob Phillips" wrote:

> 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)
>
> "Ian P" > wrote in message
> ...
> > 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" wrote:
> >
> > > 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" > 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.
> > >
> > >
> > >
>
>
>

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.
--
Kevin Vaughn


"Ian P" wrote:

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