Match Items in 2 Workbook using CountIf?

Andrew Poulsom

MrExcel MVP

Joined

Jul 21, 2002

Messages

73,092

• Mar 1, 2011

• #2

Like this?

=IF(COUNTIF([BOOK2.xls]Sheet1!$A:$A,A1),"Exist","")

 

Upvote 0

schielrn

Well-known Member

Joined

Apr 4, 2007

Messages

6,941

• Mar 1, 2011

• #3

Are you looking for something like:

=if(countif([Workbook2]Sheet1!A:A,[Workbook1]Sheet1!A1),"Exist","Doesn't Exist")

Change the workbooks and sheets to the correct names.

Hope that helps.

 

Upvote 0

J

jantzen16

New Member

Joined

Dec 7, 2010

Messages

27

• Mar 1, 2011

• #4

Hi Andrew and schielrn,

Thank you so much for the reply. I tried your code but seems something wrong with my result. it returns #VALUE in Cell B1.

Any idea why it returns that value ?

 

Upvote 0

Andrew Poulsom

MrExcel MVP

Joined

Jul 21, 2002

Messages

73,092

• Mar 1, 2011

• #5

Is the source workbook open, which COUNTIF expects? If not you can use a formula like:

=IF(SUMPRODUCT(--([BOOK2.xls]Sheet1!$A1:$A10000=A1)),"Exist","")

 

Upvote 0

J

jantzen16

New Member

Joined

Dec 7, 2010

Messages

27

• Mar 1, 2011

• #6

Hi Andrew,

It works using SUMPRODUCT(), but if possible this will solve using COUNTIF(), The source workbook is closed.

 

Upvote 0

Andrew Poulsom

MrExcel MVP

Joined

Jul 21, 2002

Messages

73,092

• Mar 1, 2011

• #7

Sorry, no. The workbook must be open for COUNTIF.

 

Upvote 0

J

jantzen16

New Member

Joined

Dec 7, 2010

Messages

27

• Mar 2, 2011

• #8

Hi Andrew,

I already tried the source workbook opened. but nothing happened. and still it returns #VALUE.

 

Upvote 0

Andrew Poulsom

MrExcel MVP

Joined

Jul 21, 2002

Messages

73,092

• Mar 2, 2011

• #9

Maybe you need to calculate - Ctrl+Alt+F9.

 

Upvote 0

J

jantzen16

New Member

Joined

Dec 7, 2010

Messages

27

• Mar 2, 2011

• #10

Hi Andrew,

I tried Ctrl+Alt+F9 and returns #REF!. does it mean Excel can't find the file?

 

Upvote 0