# Data validation formula to prevent duplicates in any cell across multiple columns

#### hyd1956

##### New Member
Hi

I'm trying to prevent duplicates from being entered into any cell in multiple columns and across any row, the formulas I've been trying haven't worked. This is what I'm using, is there anyway to do this? thanks for thelp

=COUNTIFS(\$B\$2:\$B\$6000,\$B2,\$D\$2:\$D\$6000,\$D2,\$F\$2:\$F\$6000,\$F2)<=1

### Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

#### jasonb75

##### Well-known Member
See if this one works (applied with B2 as the active cell).

=COUNTIFS(\$B\$2:\$B\$6000,B2,\$D\$2:\$D\$6000,B2,\$F\$2:\$F\$6000,B2)=1

#### hyd1956

##### New Member
Unfortunately that hasn't worked. When trying to use that formula it's not allowing me any data entry into the cells at all

#### jasonb75

##### Well-known Member
Not sure what I was thinking there, that formula was never going to work try this one instead

=(COUNTIF(\$B\$2:\$B\$6000,B2)+COUNTIF(\$D\$2:\$D\$6000,B2)+COUNTIF(\$F\$2:\$F\$6000,B2))=1

#### hyd1956

##### New Member

That is absolutely perfect, thank you

#### kmcmunn

##### New Member
Hi! I have a similar situation, but when I recreate that formula using the cell ranges for my data, it does not work. I want to prevent a duplicate entry that is dependent on column C (name), D (email), and L (activity) in which the same person/email cannot be entered for the same activity.

Any thoughts are greatly appreciated!

#### jasonb75

##### Well-known Member

Maybe this applied as validation rule to C2, then copied to the other rows / columns to be validated as needed.

=(COUNTIFS(\$C:\$C,\$C2,\$D:\$D,\$D2,\$L:\$L,\$L2)=1

#### kmcmunn

##### New Member
Thank you for the reply. The formula above does not work.

#### jasonb75

##### Well-known Member
Does not work in what way?

Allows duplicates to be entered?
Does not allow anything to be entered?
Something else?

#### kmcmunn

##### New Member
Sorry. It allows for duplicate entries to be typed into all of the cells used in the formula.

Replies
17
Views
145
Replies
7
Views
90
Replies
10
Views
101
Replies
1
Views
105
Replies
1
Views
50