Amendment to a Data Validation formula

[Pauljj]

I am using a formula in a Data validation as follows

=(E10="YES")*COUNTIF($E$10:$I$10),"YES")<3


This is looking at a row in columns E to I for the word YES and only allowing the word to appear 3 times.

If I want to change this to allow the word training as well, how could I do this please ??

Cheers

Paul

 

[Peter_SSs]

I am using a formula in a Data validation as follows

=(E10="YES")*COUNTIF($E$10:$I$10),"YES")<3


This is looking at a row in columns E to I for the word YES and only allowing the word to appear 3 times.

If I want to change this to allow the word training as well, how could I do this please ??

Cheers

Paul

Paul, can you spell it out a bit more?

Do you still require column E to be 'Yes'? or can it be 'training' as well?
Does the 3 times include column E?
Do you mean 'Yes' can occur 3 times AND 'training' can occur 3 times? .. or perhaps 'yes' 3 times OR 'training' 3 times? .. or something else?

 

[Pauljj]

Hi Peter

In columns E to I, they can enter either Yes or Training, combined this must be no more then 3

So 3 YES would be fine or 3 TRAINING or 2 YES and 1 TRAINING...or any combination thereof

In order to make this work before, I selected the range E10 to I10 and entered the formula into Data Validation. I hope this makes more sense

 

[Peter_SSs]

Hi Peter

In columns E to I, they can enter either Yes or Training, combined this must be no more then 3

So 3 YES would be fine or 3 TRAINING or 2 YES and 1 TRAINING...or any combination thereof

In order to make this work before, I selected the range E10 to I10 and entered the formula into Data Validation. I hope this makes more sense

OK, then select E10:I10 again and try this formula in Data Validation:
=(COUNTIF($E$10:$I$10,"Yes")+COUNTIF($E$10:$I$10,"training"))<=3

 

[Pauljj]

I tried this Peter, it allows me to OK the Data Validation box without any error messages but when I enter in YES 5 times, it says nothing. Even if I enter in the wrong spelling, like YYY it still allows me ??

 

[Peter_SSs]

I tried this Peter, it allows me to OK the Data Validation box without any error messages but when I enter in YES 5 times, it says nothing. Even if I enter in the wrong spelling, like YYY it still allows me ??

1. It won't allow me to enter YES 5 times. Are you sure you used the formula exactly as suggested? .. including the $ signs? .. in Data validation|Custom|Formula ? .. and you applied it to cells E10:I10?

2. It should (as is) allow you to enter wrong spelling. It is only designed to restrict the number of 'yes' and 'training' entries.

 

[Pauljj]

Ah I see Peter, I think this is the difference between my original code and the new one. I should have made that point on my original message. That people should only be allowed ...if theyre going to type anything, type YES or TRAINING

 

[Pauljj]

Does anyone else have any ideas on this one please ??

 

[NBVC]

Try:

=AND(OR(E10="Training",E10="YES"),(COUNTIF($E$10:$I$10,"YES")+COUNTIF($E$10:$I$10,"Training"))<=3)

 

[Pauljj]

Many thanks works perfectly