COUNTBLANK function

I posted a question the other day about the COUNTBLANK function because when
I used it to count the blank cells returned by the formula =IF(a1="","",a1)
it didn't work unless I deleted the contents of a1 twice.

I have now tried this on my home PC running Excel97 and I get exactly the
same fault as I do at work running Excel2000. Interestingly depending on the
order in which I set up the spreadsheet (either entering the formula first or
entering the A1 first) I can get the fault to occur on not. So I have
reached the conclusion that there is a fault with this functionality. Bob
Phillips mentioned that COUNTBLANK can't be used to count formulas returning
"" so is this a change in a later version of Excel?

Ian

COUNTBLANK() should count formulae returning blanks as well. See:


http://office.microsoft.com/en-us/as...090281033.aspx
--
Gary''s Student


"Ian P" wrote:

> I posted a question the other day about the COUNTBLANK function because when
> I used it to count the blank cells returned by the formula =IF(a1="","",a1)
> it didn't work unless I deleted the contents of a1 twice.
>
> I have now tried this on my home PC running Excel97 and I get exactly the
> same fault as I do at work running Excel2000. Interestingly depending on the
> order in which I set up the spreadsheet (either entering the formula first or
> entering the A1 first) I can get the fault to occur on not. So I have
> reached the conclusion that there is a fault with this functionality. Bob
> Phillips mentioned that COUNTBLANK can't be used to count formulas returning
> "" so is this a change in a later version of Excel?
>
> Ian

"Gary''s Student" <[email protected]> wrote in message
news:[email protected]...

> "Ian P" wrote:
>
>> I posted a question the other day about the COUNTBLANK function because
>> when
>> I used it to count the blank cells returned by the formula
>> =IF(a1="","",a1)
>> it didn't work unless I deleted the contents of a1 twice.
>>
>> I have now tried this on my home PC running Excel97 and I get exactly the
>> same fault as I do at work running Excel2000. Interestingly depending on
>> the
>> order in which I set up the spreadsheet (either entering the formula
>> first or
>> entering the A1 first) I can get the fault to occur on not. So I have
>> reached the conclusion that there is a fault with this functionality.
>> Bob
>> Phillips mentioned that COUNTBLANK can't be used to count formulas
>> returning
>> "" so is this a change in a later version of Excel?

> COUNTBLANK() should count formulae returning blanks as well. See:
>
> http://office.microsoft.com/en-us/as...090281033.aspx

http://exceltips.vitalnews.com/Pages...ank_Value.html and
http://exceltips.vitalnews.com/Pages...ank_Cells.html
suggest that this hasn't changed between 97, 2000, & 2003.
--
David Biddulph

I'm on an XL97 machine today and the Countblank() function *does* count zero
length strings (""), which are returned by formulas.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Gary''s Student" <[email protected]> wrote in message
news:[email protected]...
> COUNTBLANK() should count formulae returning blanks as well. See:
>
>
> http://office.microsoft.com/en-us/as...090281033.aspx
> --
> Gary''s Student
>
>
> "Ian P" wrote:
>
> > I posted a question the other day about the COUNTBLANK function because
when
> > I used it to count the blank cells returned by the formula
=IF(a1="","",a1)
> > it didn't work unless I deleted the contents of a1 twice.
> >
> > I have now tried this on my home PC running Excel97 and I get exactly
the
> > same fault as I do at work running Excel2000. Interestingly depending
on the
> > order in which I set up the spreadsheet (either entering the formula
first or
> > entering the A1 first) I can get the fault to occur on not. So I have
> > reached the conclusion that there is a fault with this functionality.
Bob
> > Phillips mentioned that COUNTBLANK can't be used to count formulas
returning
> > "" so is this a change in a later version of Excel?
> >
> > Ian

Just to add to what's already been posted:

From Excel help:

COUNTBLANK
Counts empty cells in a specified range of cells.
Cells with formulas that return "" (empty text) are also counted.

ISBLANK
The ISBLANK function returns the logical value TRUE if value is a reference
to an empty cell; otherwise it returns FALSE.

A1 = =""

=COUNTBLANK(A1) = 1
=ISBLANK(A1) = FALSE

The should have named ISBLANK ISEMPTY

Biff

"Ian P" <[email protected]> wrote in message
news:[email protected]...
>I posted a question the other day about the COUNTBLANK function because
>when
> I used it to count the blank cells returned by the formula
> =IF(a1="","",a1)
> it didn't work unless I deleted the contents of a1 twice.
>
> I have now tried this on my home PC running Excel97 and I get exactly the
> same fault as I do at work running Excel2000. Interestingly depending on
> the
> order in which I set up the spreadsheet (either entering the formula first
> or
> entering the A1 first) I can get the fault to occur on not. So I have
> reached the conclusion that there is a fault with this functionality. Bob
> Phillips mentioned that COUNTBLANK can't be used to count formulas
> returning
> "" so is this a change in a later version of Excel?
>
> Ian