SOLVED

Data Validation and Countif

Copper Contributor

Hi everyone, I'm trying to make a table of some names and I want to limit the users entering only 10 of the same name. I used data validation and countif function but it doesn't work. As you may guess I'm not that experienced in Excel so I appreciate any and every bit of help. Thanks in advance.

Example file is attached.

7 Replies

 

=IF(COUNTIF($A$1:$A$5900,A3)<=10,1,0)

@mangatic try this

@mangatic  Let's say you are entering names in column C then select column C and select Data Validation.  Then choose "Custom" and enter the following formula:

=(countif(c:c,c1)<11)

that will make sure a max of 10 identical entries can be entered in col C.

you can also choose warning and custom messages to prompt to the user.

best response confirmed by mangatic (Copper Contributor)
Solution

file attached

Thanks for taking your time and replying. I couldn't manage to make this work. Maybe because my Excel version is old.

Thanks for replying but it didn't work. Got an error sayinG Common Function Mistake.

@mtarler 

Thank you very much, this actually works but I don't know the solution cause I can't see Data Validation. When I try to see, it erases Data Validation settings. And your formula in your first reply got an error as I mentioned.
not sure why, but I used the same formula.
could be due to older version, not sure
1 best response

Accepted Solutions
best response confirmed by mangatic (Copper Contributor)
Solution