Match Items in 2 Workbook using CountIf?

[jantzen16]

Greetings to all,


I am having a problem with matching the value of first workbook and second workbook, for example

Workbook1: A1 = Blue B1 = using the CountIf formula

Workbook2: A8 = Blue

now, in Workbook1 B1 will have a value "Exist".
Any ideas?

Thank you in advance..

 

[Andrew Poulsom]

Like this?

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

 

[schielrn]

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.

 

[jantzen16]

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 ?

 

[Andrew Poulsom]

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","")

 

[jantzen16]

Hi Andrew,

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

 

[Andrew Poulsom]

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

 

[jantzen16]

Hi Andrew,

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

 

[Andrew Poulsom]

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

 

[jantzen16]

Hi Andrew,

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