CountIFS in data Validation

[Egiha64]

Trying to check for duplicates in multiple columns using data validation but its not functioning correctly.
Formula:
=CountIFS($A$2:$A$6000, A2,$B$2:$B$6000,B2,$C$2:$C$6000,C2)=1

When I try and enter unique data it gives error code I set up.

 

[Joe4]

You cannot attach files here. If you wish to share a file, you can upload it to a file sharing site (like DropBox), and post the link here.
Note that my workplace security forbids downloading files from the internet, so I can not download it until later when I am at my home computer.

But can't you just give me an example based on the sample data you posted? An example where it is not working as expected?
Just tell me what values you are entering that cause it to behave improperly.

 

[Egiha64]

Ok, using the dataset I sent earlier,
When I enter a unique value in column X, AM or BB it should enter with no error. When a duplicate value is entered in either column it should trigger the error message and prevent entry.
Here some examples:
I entered 206201 in X7 and it was not rejected even though it existed in AM2.
I entered 589668 in AM8 and it was not rejected even though it existed in BB8.

I entered the formula =COUNTIFS($X$2:$X$6018,$X2,$AM$2:$AM$6018,$AM2,$BB$2:$BB$6018,$BB2)=1 as a custom validation with error alerts.

See matching highlighted text to visualize the problem.

Appreciate the help Joe.

 

[Joe4]

OK, the way your Validation Rule is written, it is not checking that way.

It looks like you want it to check to see if a single Serial Number exists ANYWHERE across any of those three columns. Is that correct?
The way you have written your rule, it is checking for duplicates when all THREE column entries are entered, and they are entered in the exact same order.

So, if you had a row where 1-2-3 were entered across those three columns (in that particular order), if you tried to enter 1-2-3 again (in that exact order), the rule would fire, and you would get that error message. However, if you entered those same three numbers in a different order (like 3-2-1), it would allow it (because your rule is only checking for the value you entered in column X in the X column, and not the other two).

Let me ask you a question. Is it possible for these Serial Number to appear in ANY other columns besides X, AM, and BB (in between columns A and BB)?
If not, then simply use this Validation formulas:
For range X2:X6018, use: =COUNTIF($X$2:$BB$6018,X2)=1
For range AM2:AM6018, use: =COUNTIF($X$2:$BB$6018,AM2)=1
For range BB2:BB6018, use: =COUNTIF($X$2:$BB$6018,BB2)=1

 

[Egiha64]

Yes, you got it but the Serial Numbers are restricted just in those columns (A, AM,BB)

Using your formulas, if I enter a value in column X, will it check if there's a duplicate in column AM and BB?

 

[Joe4]

Yes, you got it but the Serial Numbers are restricted just in those columns (A, AM,BB)

Excellent. Then we should be able to use the formulas I posted in the previous post.

Using your formulas, if I enter a value in column X, will it check if there's a duplicate in column AM and BB?

Yes, try it and see. We are actually now looking for that value in the range $X$2:$BB$6018.
So we are searching ALL those columns, not just column X.

 

[Egiha64]

It didn't work.
Does the validation have to be across all the columns between the Serial Number columns for it to work. I'm only applying it to X, AM, BB.

 

[Egiha64]

Hey Joe, the formula worked when I changed it to =1 instead of >1.
You're the man, Joe. Appreciate all your help.

 

[Joe4]

Hey Joe, the formula worked when I changed it to =1 instead of >1.
You're the man, Joe. Appreciate all your help.

Yeah, I just noticed that typo on my part and was about to post it. Glad you found it!
I will go back and fix that post above.

Glad it is now working the way you want!