counta in array formula not working

Hi,

My series B13:B20 contains zero's and text values. I wish to count the text
values but not zero's or blanks.

I have tried the following as an array but its counting all cells in range
with the result = 8 which is not correct (as there are zeros and balnks in
the series).

=COUNTA(IF(B13:B20<>0,"A",0))

Any ideas?

Bruce

Rick
Fri Mar 14 16:48:22 CDT 2008

Does this do what you want?

=COUNTA(B13:B20)-COUNT(B13:B20)

Rick


"Bruce" <Bruce@discussions.microsoft.com> wrote in message
news:44B8B020-0D52-4978-903E-D36A900371FA@microsoft.com...
> Hi,
>
> My series B13:B20 contains zero's and text values. I wish to count the
> text
> values but not zero's or blanks.
>
> I have tried the following as an array but its counting all cells in range
> with the result = 8 which is not correct (as there are zeros and balnks in
> the series).
>
> =COUNTA(IF(B13:B20<>0,"A",0))
>
> Any ideas?
>
> Bruce

TomHutchins
Fri Mar 14 16:48:01 CDT 2008

How about
=SUMPRODUCT(--(LEN(B13:B20)>0),--(B13:B20<>0))

Hope this helps,

Hutch

"Bruce" wrote:

> Hi,
>
> My series B13:B20 contains zero's and text values. I wish to count the text
> values but not zero's or blanks.
>
> I have tried the following as an array but its counting all cells in range
> with the result = 8 which is not correct (as there are zeros and balnks in
> the series).
>
> =COUNTA(IF(B13:B20<>0,"A",0))
>
> Any ideas?
>
> Bruce

T
Fri Mar 14 17:02:27 CDT 2008

>I wish to count the text values but not zero's or blanks

If blanks means empty cells:

=COUNTIF(B13:B20,"*")


--
Biff
Microsoft Excel MVP


"Bruce" <Bruce@discussions.microsoft.com> wrote in message
news:44B8B020-0D52-4978-903E-D36A900371FA@microsoft.com...
> Hi,
>
> My series B13:B20 contains zero's and text values. I wish to count the
> text
> values but not zero's or blanks.
>
> I have tried the following as an array but its counting all cells in range
> with the result = 8 which is not correct (as there are zeros and balnks in
> the series).
>
> =COUNTA(IF(B13:B20<>0,"A",0))
>
> Any ideas?
>
> Bruce