COUNTIF command in Microsoft excel 2010

Standard excel document with 2 sheets in office 2010
How can 2 rows be compare and highlight the values, when the value shows in both list.

Example
Sheet 1 | Sheet 2

A               A
1234         4324
2222         3338
3333         1234
4444         4556

Now I excel need to automatically highlight the value 1234 because it show in both rows.
Current I try to get this done by using the COUNTIF statement

=COUNTIF(sheet1!$A$2:$A$3,A2)>1

As soon I hit enter I got an error message, the formula you typed in contains an error.
test.xlsx
LVL 2
Patrick de GelderSupport engineerAsked:
Who is Participating?
 
NorieConnect With a Mentor VBA ExpertCommented:
I selected A2:A58 on Blad1, went to conditional formatting, selected Use a formula to determine which cells to format, entered this formula,

=COUNTIF(Blad2!A:A, A2)

and formatted for a red fill.

See the attached workbook for the results.
EE-CF-Test.xlsx
0
 
NorieVBA ExpertCommented:
I don't see anything wrong with the formula.

Where are you using the formula?
0
 
Patrick de GelderSupport engineerAuthor Commented:
Standard edition from Microsoft excel 2010,

First selected column A on sheet 2, then I go to the option conditional formatting, then fill in the formula and selected a specific color and as soon I click on it reports the above error.
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
NorieVBA ExpertCommented:
Does the formula work if you enter it on a worksheet?
0
 
Patrick de GelderSupport engineerAuthor Commented:
No have tried that any other statement seems to work fine
0
 
Patrick de GelderSupport engineerAuthor Commented:
Thanks, you help me a lot,
It's very strange it must be a problem with office I checked the formula in your sheet and it works well :). Then in a new empty document same formula it doesn't work.
But then there is no reason to troubleshoot this further as this pc is going soon anyway.

For further reference we confirmed that the formula works. :-)
0
 
Patrick de GelderSupport engineerAuthor Commented:
Thanks for helping get this problem fixed
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.