simple counting gone awry
 

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

simple counting gone awry
 

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

On Nov 10, 12:04*am, Nondisclosure007
wrote:
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

simple counting gone awry
 

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


Gord Dibben MS Excel MVP

On Mon, 9 Nov 2009 16:04:10 -0800 (PST), Nondisclosure007
wrote:

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

simple counting gone awry
 

"Nondisclosure007" wrote:
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 -----

"Nondisclosure007" wrote in message
...
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