using a cell as criteria instead of cell contents
 

I am using the formula =COUNTIF($A$1:$A$2500,B1), but it gives a value of 0.
If I use the contents of B1 (ml12345) it gives how many times it appears,
which is what I want. Is there a way to use the cell name (B1) to give the
value I want? I am trying to see how many times each policy number appears. I
want to autofill down, since I have about 300 different policy numbers.

using a cell as criteria instead of cell contents
 

Hi Jason,
I give yesterday the solution to your formula problem when was directionet
to Sheet1, did you rate the answer??,
anyway I don't understant what you want to achieve could you give an example
with different numbers

"Jason K" wrote:

I am using the formula =COUNTIF($A$1:$A$2500,B1), but it gives a value of 0.
If I use the contents of B1 (ml12345) it gives how many times it appears,
which is what I want. Is there a way to use the cell name (B1) to give the
value I want? I am trying to see how many times each policy number appears. I
want to autofill down, since I have about 300 different policy numbers.

using a cell as criteria instead of cell contents
 

Jason,

Try this

=SUMPRODUCT(--(TRIM($A$1:$A$2500)=TRIM(B1)))

Mike

"Jason K" wrote:

I am using the formula =COUNTIF($A$1:$A$2500,B1), but it gives a value of 0.
If I use the contents of B1 (ml12345) it gives how many times it appears,
which is what I want. Is there a way to use the cell name (B1) to give the
value I want? I am trying to see how many times each policy number appears. I
want to autofill down, since I have about 300 different policy numbers.

using a cell as criteria instead of cell contents
 

Trim???

=COUNTIF($A$1:$A$2500,trim(B1))

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Jason K" wrote in message
...
I am using the formula =COUNTIF($A$1:$A$2500,B1), but it gives a value of
0.
If I use the contents of B1 (ml12345) it gives how many times it appears,
which is what I want. Is there a way to use the cell name (B1) to give the
value I want? I am trying to see how many times each policy number
appears. I
want to autofill down, since I have about 300 different policy numbers.

using a cell as criteria instead of cell contents
 

I quess i was confused myself, I found out that I needed to trim the one
cell. After I did that it worked. Thank You.

"Eduardo" wrote:

Hi Jason,
I give yesterday the solution to your formula problem when was directionet
to Sheet1, did you rate the answer??,
anyway I don't understant what you want to achieve could you give an example
with different numbers

"Jason K" wrote:

I am using the formula =COUNTIF($A$1:$A$2500,B1), but it gives a value of 0.
If I use the contents of B1 (ml12345) it gives how many times it appears,
which is what I want. Is there a way to use the cell name (B1) to give the
value I want? I am trying to see how many times each policy number appears. I
want to autofill down, since I have about 300 different policy numbers.