Comparing Tabs

[cra88]

I have 2 sheets, the first has a list of model numbers I want to search for
in sheet2. I then want to total the number of items in sheet 2 that match the
list in sheet 1.

Thanks

 

[Ashish Mathur]

Hi,

In a spare column (say, stating from C5) on sheet1, use the formula
=countif(B5:B100,A5) and copy down (till say, cell C50). This will return
you the number of times the items in Sheet1 are present in Sheet2. Now to
know total of items, use the foll formula =countif(C5:C50,">0")

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[smartin]

I have 2 sheets, the first has a list of model numbers I want to search for
in sheet2. I then want to total the number of items in sheet 2 that match the
list in sheet 1.

Thanks

Assume setup:

Sheet1!A1:A4
a
b
c
d

Sheet2!A1:A10
a
a
a
b
b
c
d
x
y
z

One way is to calculate each count in Sheet1!B1
=COUNTIF(Sheet2!A:A,Sheet1!A1)
fill down, then total column B.

Another way accomplishes this in one step using an array* formula:
=SUM(--ISNUMBER(MATCH(Sheet2!A1:A10,Sheet1!A1:A4,0)))

Result = 7

*Commmit the array formula by pressing Ctrl+Shift+Enter. Do not just
press Enter or Tab.