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]

Welcome to the Board!

Are you applying this across columns A, B, and C?
If so, you need to lock the column references of the criteria arguments, like this:
=CountIFS($A$2:$A$6000,$A2,$B$2:$B$6000,$B2,$C$2:$C$6000,$C2)=1

 

[Egiha64]

Thanks for the welcome Joe! It still didn't work, but I've attached some detail.

The Columns are $X for BV1 S/N and $AM for BV2 S/N but it could be one or multiple column references.
Goal: Check for duplicate Serial Number in both columns when I enter a new value in either column.

So far, the check works only in the first column in the formula, in this case the $X column. If I enter a duplicate Serial Number in $X that already exist in the other ($AM) column, it allows an entry and does not trigger the error message.

Let me know if you understand me dilemma. I'll try and explain better.

BV1 S/N	BV1 Device ID	BV1 Present	BV2 Pre-Wire Install Status	BV2 S/N
205284	BMV01596	1	Full
206507	BMV00900	1	Full
205456	BMV02697	1	Full
206188	BMV01002	1	Full
206057	BMV01577	1	Full

 

[Joe4]

Since you are not really dealing with columns A, B, and C, please do the following:
1. Post the actual formula you are using
2. Let us know the exact range you are applying that data validation formula to (I want the EXACT cell address of the range)
3. Let us know what columns you are showing us in your previous post (column letters for each one)

 

[Egiha64]

Formula I'm using: =COUNTIFS($X$2:$X$6018,$X2,$AM$2:$AM$6018,$AM2,$BB$2:$BB$6018,$BB2)=1
Range= X2:X6018, AM2:AM6018, BB2:BB6018
X Column is BV1 S/N, AM Column is BV2 S/N and BB column is BV3 S/N. BB column not shown in previous post.

 

[Joe4]

BB column not shown in previous post.

For the example you posted earlier, can you post a sample of the data in column BB looks like?
I would like to reconstruct the sheet layout exactly like yours, so I can test your exact conditions on my side.

 

[Egiha64]

Here is a sample:

The rest of the columns have other data like P/N, and dates. But I really need the data validation on the serial numbers to prevent duplication on data entry.
So, if I enter a new serial in either column it would validate against the others.

X​	AM​	BB​
BV1 S/N	BV2 S/N	BV3 S/N
205284	206201	311555
206507	208454	554889
205456	206211	321255
206188​	274555	274123
206057	212444	208456
	245622	367891
		589668

 

[Joe4]

OK, so based on the example you just posted, can you tell me of a new entry that doesn't work the way you expect?
When I add a new combination of three values like that, it works just fine.
If I duplicate that exact combination of three values again, then I get the error message.
So it seems to be working to me.

 

[Egiha64]

Can I insert a copy of the file?

 

[Egiha64]

I can email it also