1

How to prevent a user entering a word if the number of occurrences of the word in a column already exceeds five?

In other words, how to put a limit on word occurrences?

pnuts
  • 58,317
  • 11
  • 87
  • 139
N S
  • 13
  • 3

1 Answers1

1

If the relevant column is D, select it, Data, Validation..., Cell range: D:D, Criteria: Custom formula is 

 =countif(D$1:D1,D1)<6

On invalid data: Reject input, Save.

SO33397203 example

pnuts
  • 58,317
  • 11
  • 87
  • 139
  • Thanks pnuts! I think, I do have an issue with your code. So my column (col B) is called Media Outlet where other users can write which media they have contacted. For ex: "Houston Chronicles". Whenever a user enters the "Houston Chronicles" sixth time (on the same column "Media Outlet"), it should not allow the user to enter the data and pop up saying "this media outlet has been exceeded the limit". I don't want to contact the same media more than 5 times. I have tried =countif(B$2:B,B1)<6 , but it didn't work. Thanks again! – N S Oct 29 '15 at 03:56
  • 1
    Suddenly, it worked for me too :). Thanks a lot, puts! Upvote! I don't have enough reputation to upvote, sorry – N S Oct 29 '15 at 04:32