counta in array formula not working

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Bruce - 14 Mar 2008 22:40 GMT

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

Reply to this Message

Tom Hutchins - 14 Mar 2008 22:48 GMT

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

Hope this helps,

Hutch

> Hi,
>
[quoted text clipped - 10 lines]
>
> Bruce

Reply to this Message

Rick Rothstein (MVP - VB) - 14 Mar 2008 22:48 GMT

Does this do what you want?

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

Rick

> Hi,
>
[quoted text clipped - 11 lines]
>
> Bruce

Reply to this Message

T. Valko - 14 Mar 2008 23:02 GMT

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

If blanks means empty cells:

=COUNTIF(B13:B20,"*")

Signature

Biff
Microsoft Excel MVP

> Hi,
>
[quoted text clipped - 11 lines]
>
> Bruce

Reply to this Message