how does this COUNTIF formula work for Data Validation?

[srush]

I needed advice on how to prevent duplicate values appearing in my data. The following formula solves this for me - but I can't find an explanation as to why. Can anyone advise please?

Under data validation, I added an "allow" "custom" formula "=COUNTIF (C:C,C6)<2" , and it's working - but I don't know why! It has something to do with the "<2".

Any ideas?
Thanks
Steve

[PaulM100]

The formula counts how many occurences of the value in C6 exists in the range C:C. 1=unique, 2 and over 2 = duplicates. So <2 part is saying that it will allow only unique entries.

[6StringJazzer]

Your post does not comply with Rule 1 of our Forum Rules. Please update your title to tell us something about your specific question. Describe your problem, not your anticipated solution. We ask this for two reasons:

1. Members scan the list of threads to decide where they can help. The title helps us decide which questions we have the knowledge to solve.
2. After a question is answered, the title helps people with similar questions find it in a search.

Use terms appropriate to a Google search. Examples of poor thread titles are Please Help, Urgent, Need Help, Formula Problem, Excel Question, Code Problem, and Need Advice.

To change a Title go to your first post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7.

[srush]

The formula counts how many occurences of the value in C6 exists in the range C:C. 1=unique, 2 and over 2 = duplicates. So <2 part is saying that it will allow only unique entries.

Thanks. Is that documented somewhere ? Not at all
Obvious..

[6StringJazzer]

Your post does not comply with Rule 1 of our Forum Rules. Please update your title to tell us something about your specific question.

It's still a poor title. I gave up and changed it for you but please give this serious thought next time you start a thread.

[PaulM100]

Here you can find it explained;

http://www.excel-easy.com/examples/p...e-entries.html

[6StringJazzer]

Thanks. Is that documented somewhere ? Not at all
Obvious..

It is documented everywhere.

How To Prevent Duplicate Entries In A Column In Excel Using Custom Data Validation and COUNTIF