COUNTA failing?

D

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?

Dick Penny
 
F

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
 
D

Dave Peterson

You sure you didn't mean to use =countif()?
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?

Dick Penny
Click to expand...
 
B

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

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")
 
Top