COUNTIF Formula

[nemonat]

Hello,
I am trying to use the COUNTIF formula to compare between text column of 2different sheets; sheet1 & sheet 2 (A column in each).
When it finds a cell that is identical to both columns, it marks an "X" next to that cell.
Ideas anyone?
Thanks much!

[NBVC]

try something like,

in Sheet1, B2,

=If(Countif(Sheet2!A:A,A2),"X","") copied down

where Sheet2!A:A is the column to compare against A2 on the current sheet....

[daddylonglegs]

Do you want to compare A1 with A1 and A2 with A2 etc or do you want to look for A1 in one sheet in the whole A column in the other?

If it's the latter try this formula in B1 sheet1 copied down

=IF(COUNTIF('sheet2'!A:A,A1),"x","")

[nemonat]

Thanks for replying back.
I want to look for A1, A2 , A3, etc in one sheet in the whole A column in the other? this formula below you suggested didn’t work for some reason...It doesn’t mark an X where I planted the identical items.
=IF(COUNTIF('sheet2'!A:A,A1),"x","")
I copied the formula to all the cells but I either get an X in all the cells or an empty row.
How come

[NBVC]

The match has to be identical.

Also, make sure your other sheet's name is Sheet2, otherwise change it in the fomrula to match the sheetname.

Finally, make sure you are running calculations in Automatic mode....go to Tools|Options and ensure Automatic is selected in the Calculation tab.

[nemonat]

I changed the sheet name is the formula. And I am running calculations in Automatic mode.

I tried the formula on a smaller version and it still didnt work.
I am using Excel 2003. The Compare b\w sheets is selected so I can see both of the sheets side by side.
Here is a test:
In sheet1 I have
Column A
Sara
Mike
Bob
In cell B1 I have your suggested formula copies all the way through to cell B3

Sheet 2
Column A
Sara
Natan
Mike
George

Sara & Mike in Sheet1 suppose to have a X next to them, right?
What am I doing wrong?

[NBVC]

I am not sure what you are doing wrong, if anything...

See attached sample....

[nemonat]

It's ok now. Thank you for sending an example.
I appreciate your help.

[nemonat]

One more question if I may,
What if I am adding another sheet to compare with? How do I add that 3rd one to the formula you suggested?
I might even have 5 sheets compare b\w.
Thx

[NBVC]

One more question if I may,
What if I am adding another sheet to compare with? How do I add that 3rd one to the formula you suggested?
I might even have 5 sheets compare b\w.
Thx

The simplest way would be to compare each sheet by stringing Countif's in an OR statement... If a match is found in any sheet, then the x is returned.

e.g.

Please Login or Register  to view this content.

[nemonat]

Excellent. Thank you very much

[nemonat]

"If a match is found in any sheet, then the x is returned"
How do I add that in the forumla?

I get an #VALUE error when I try to use it like this:

IF(OR(COUNTIF(Sheet2!A:A,A1),"x",""),COUNTIF('sheet3'!A:A,A1))

[NBVC]

s/b

IF(OR(COUNTIF(Sheet2!A:A,A1),COUNTIF('sheet3'!A:A,A1)),"x","")