Compare lists - count # records

[Jan]

Hi,

Using Excel 2003. I have two lists.

List A - has one record of ID.
List B - may have more than one record of ID.

I need a formula on List A to count the number of records from list B when a
match is found for the ID in list A.

Can anyone help with a formula?

TIA

 

[Peo Sjoblom]

Assume list A starts in A2 and list B is in Sheet2 in B2:B1000

Insert a new column next to list A unless you have one and put this in the
first adjacent cell B2 in my example


=COUNTIF(Sheet2!$B$2:$B$1000,A2)

then press enter. Select B2 again and move the cursor to the lower right
corner of B2 and when the cross changes from a thick to a thin double click
the mouse to copy down the formula

The apply the auto filter and filter on column B (the same B where we put
the formula) and select custom and greater than zero


--


Regards,


Peo Sjoblom