simple counting gone awry

[Nondisclosure007]

I'm trying to count the number of occurances in a range based on the
value in the cell.

for example:
a1 has 0.001
a2 has 0.020
...and so on.

I've tried using =countif(myrange,"=a1"), but I get 0 in return. When
I know my range has 0.001 in it.

what the heck am I doing wrong?

Excel 2007

 

[Pete_UK]

Try it like this:

=countif(myrange,a1)

The way you had it the formula was looking for the literal string
"a1", rather than the contents of A1. The = is implied, and so not
needed.

Hope this helps.

Pete

 

[Gord Dibben]

=COUNTIF(range,A1) works for me as long as you don't have a rounding
problem.


Gord Dibben MS Excel MVP

 

[Joe User]

I've tried using =countif(myrange,"=a1"), but I get 0
in return. When I know my range has 0.001 in it.
what the heck am I doing wrong?

For one thing, you should write COUNTIF(myrange,"="&A1), although
COUNTIF(myrange,A1) would suffice in this case because "=" is implicit.

But even so, COUNTIF might return some suprising result because not all
values that display as 0.001 are equal.

Perhaps the following will work more reliably for you:

=SUMPRODUCT(--(ROUND(myrange,3)=ROUND(A1,3)))

Change the 3 to 4 or something larger if your intent is to distinguish
0.0011 from 0.0010, for example.


----- original message -----