SOLVED

Data Validation and Countif

%3CLINGO-SUB%20id%3D%22lingo-sub-1386098%22%20slang%3D%22en-US%22%3EData%20Validation%20and%20Countif%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1386098%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20everyone%2C%20I'm%20trying%20to%20make%20a%20table%20of%20some%20names%20and%20I%20want%20to%20limit%20the%20users%20entering%20only%2010%20of%20the%20same%20name.%20I%20used%20data%20validation%20and%20countif%20function%20but%20it%20doesn't%20work.%20As%20you%20may%20guess%20I'm%20not%20that%20experienced%20in%20Excel%20so%20I%20appreciate%20any%20and%20every%20bit%20of%20help.%20Thanks%20in%20advance.%3C%2FP%3E%3CP%3EExample%20file%20is%20attached.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1386098%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1386179%22%20slang%3D%22en-US%22%3ERe%3A%20Data%20Validation%20and%20Countif%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1386179%22%20slang%3D%22en-US%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DIF(COUNTIF(%24A%241%3A%24A%245900%2CA3)%26lt%3B%3D10%2C1%2C0)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F666180%22%20target%3D%22_blank%22%3E%40mangatic%3C%2FA%3E%26nbsp%3Btry%20this%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1386185%22%20slang%3D%22en-US%22%3ERe%3A%20Data%20Validation%20and%20Countif%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1386185%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F666180%22%20target%3D%22_blank%22%3E%40mangatic%3C%2FA%3E%26nbsp%3B%20Let's%20say%20you%20are%20entering%20names%20in%20column%20C%20then%20select%20column%20C%20and%20select%20Data%20Validation.%26nbsp%3B%20Then%20choose%20%22Custom%22%20and%20enter%20the%20following%20formula%3A%3C%2FP%3E%3CP%3E%3D(countif(c%3Ac%2Cc1)%26lt%3B11)%3C%2FP%3E%3CP%3Ethat%20will%20make%20sure%20a%20max%20of%2010%20%3CU%3Eidentical%3C%2FU%3E%20entries%20can%20be%20entered%20in%20col%20C.%3C%2FP%3E%3CP%3Eyou%20can%20also%20choose%20warning%20and%20custom%20messages%20to%20prompt%20to%20the%20user.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1386194%22%20slang%3D%22en-US%22%3ERe%3A%20Data%20Validation%20and%20Countif%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1386194%22%20slang%3D%22en-US%22%3E%3CP%3Efile%20attached%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1386218%22%20slang%3D%22en-US%22%3ERe%3A%20Data%20Validation%20and%20Countif%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1386218%22%20slang%3D%22en-US%22%3EThanks%20for%20taking%20your%20time%20and%20replying.%20I%20couldn't%20manage%20to%20make%20this%20work.%20Maybe%20because%20my%20Excel%20version%20is%20old.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1386233%22%20slang%3D%22en-US%22%3ERe%3A%20Data%20Validation%20and%20Countif%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1386233%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%20for%20replying%20but%20it%20didn't%20work.%20Got%20an%20error%20sayinG%20Common%20Function%20Mistake.%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F570951%22%20target%3D%22_blank%22%3E%40mtarler%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1386242%22%20slang%3D%22en-US%22%3ERe%3A%20Data%20Validation%20and%20Countif%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1386242%22%20slang%3D%22en-US%22%3EThank%20you%20very%20much%2C%20this%20actually%20works%20but%20I%20don't%20know%20the%20solution%20cause%20I%20can't%20see%20Data%20Validation.%20When%20I%20try%20to%20see%2C%20it%20erases%20Data%20Validation%20settings.%20And%20your%20formula%20in%20your%20first%20reply%20got%20an%20error%20as%20I%20mentioned.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1386378%22%20slang%3D%22en-US%22%3ERe%3A%20Data%20Validation%20and%20Countif%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1386378%22%20slang%3D%22en-US%22%3Enot%20sure%20why%2C%20but%20I%20used%20the%20same%20formula.%3CBR%20%2F%3Ecould%20be%20due%20to%20older%20version%2C%20not%20sure%3C%2FLINGO-BODY%3E
Highlighted
Occasional 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
Highlighted

 

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

@mangatic try this

Highlighted

@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.

Highlighted
Best Response confirmed by mangatic (Occasional Contributor)
Solution

file attached

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

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

@mtarler 

Highlighted
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.
Highlighted
not sure why, but I used the same formula.
could be due to older version, not sure