I am trying to see if a policy number (ex. ml001122) appears on a sheet. I
have a list of about 300, and want to know if they appear on another sheet
and how many times they appear. I have tried using the formula
=COUNTIF('SHEET1'!A1:'SHEET1'!A2500,A1) but it keeps coming back as 0. The
first sheet has more than two thousand rows, and on the second sheet I have
about 300 policy numbers to check. I need to see if those numbers are on the
first sheet.

Thanks for any help

Hi jason, The problem was in the formula
try =COUNTIF('SHEET1'!A1:!A2500,A1)
"Jason K" wrote:

> I am trying to see if a policy number (ex. ml001122) appears on a sheet. I
> have a list of about 300, and want to know if they appear on another sheet
> and how many times they appear. I have tried using the formula
> =COUNTIF('SHEET1'!A1:'SHEET1'!A2500,A1) but it keeps coming back as 0. The
> first sheet has more than two thousand rows, and on the second sheet I have
> about 300 policy numbers to check. I need to see if those numbers are on the
> first sheet.
>
> Thanks for any help

The formula should be
=COUNTIF('SHEET1!A1:A,A1),

"Jason K" wrote:

> I am trying to see if a policy number (ex. ml001122) appears on a sheet. I
> have a list of about 300, and want to know if they appear on another sheet
> and how many times they appear. I have tried using the formula
> =COUNTIF('SHEET1'!A1:'SHEET1'!A2500,A1) but it keeps coming back as 0. The
> first sheet has more than two thousand rows, and on the second sheet I have
> about 300 policy numbers to check. I need to see if those numbers are on the
> first sheet.
>
> Thanks for any help

Eduardo is right. you should COUNTIF(Sheet1!$A$1:$A$2500,Sheet2!A1).

Another issue is to double check; confirm the format the policy # are
written out in your sheet 1 is exactly the same as your data worksheet.... a
good way to test is to copy cell A1 in your data sheet and paste in the
"find" dialogue box (after clicking ctrl+F, then find to see if the policy
actually exists in your sheet 1.

wish you luck.

"Jason K" wrote:

> I am trying to see if a policy number (ex. ml001122) appears on a sheet. I
> have a list of about 300, and want to know if they appear on another sheet
> and how many times they appear. I have tried using the formula
> =COUNTIF('SHEET1'!A1:'SHEET1'!A2500,A1) but it keeps coming back as 0. The
> first sheet has more than two thousand rows, and on the second sheet I have
> about 300 policy numbers to check. I need to see if those numbers are on the
> first sheet.
>
> Thanks for any help