COUNTA failing?

[dpenny]

In a brand new worksheet in cell D1 I type =counta(d2:d4,"jones").
I get the answer 1 (one). If I then type jones into cell d3, I get 2.

Why? Is there a bug in counta?

**** Penny

[fcastrofilippo]

The function is doing what it is suppose to do. The "counta" function counts any string in the selected range.

Your formula has two strings in the range.

Frank

[Dave Peterson]

You sure you didn't mean to use =countif()?

dpenny wrote:
>
> In a brand new worksheet in cell D1 I type =counta(d2:d4,"jones").
> I get the answer 1 (one). If I then type jones into cell d3, I get 2.
>
> Why? Is there a bug in counta?
>
> **** Penny
>
> --
> dpenny
> ------------------------------------------------------------------------
> dpenny's Profile: http://www.excelforum.com/member.php...o&userid=19708
> View this thread: http://www.excelforum.com/showthread...hreadid=564365

--

Dave Peterson

[Biff]

Yeah, but what's strange about this is:

=counta(d2:d4,"jones")

Jones evaluates as a #VALUE! error.

Depending on what version of Excel you have (XP and up) use the
Tools>Formula Auditiing>Evaluate Formula command.

The formula evaluates straight through to the result of 1.

Now, click the Insert Function icon.

Notice value2 "jones" = #VALUE!

Biff

"fcastrofilippo"
<[email protected]> wrote in
message news:[email protected]...
>
> The function is doing what it is suppose to do. The "counta" function
> counts any string in the selected range.
>
> Your formula has two strings in the range.
>
> Frank
>
>
> --
> fcastrofilippo
> ------------------------------------------------------------------------
> fcastrofilippo's Profile:
> http://www.excelforum.com/member.php...o&userid=36542
> View this thread: http://www.excelforum.com/showthread...hreadid=564365
>

[Sloth]

COUNTA simply counts the number of nonblanks in the range(s) you specify and
any values you input as arguments. In your case there is nothing in D2:D4
and you specified one other argument. Therefore the result of 1 is correct.
You could put anything in cell d3 and the result would always change to 2.
You can even insert a value that results in an error (like =NA() or =1/0).

As Dave mentioned, I think you are using the wrong function. If you want it
to count the number of cells in D2:D4 that contain "jones" use this formula

=COUNTIF(D2:D4,"Jones")