# Amendment to a Data Validation formula

#### Pauljj

##### Well-known Member
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

### Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
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?

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

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

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 ??

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.

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

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

Try:

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

Many thanks works perfectly

Replies
6
Views
316
Replies
1
Views
162
Replies
0
Views
153
Replies
1
Views
82
Replies
3
Views
144

1,211,880
Messages
6,104,547
Members
447,915
Latest member
jpearson

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back