Data validation referring to cell that has count if function

[SlinkRN]

Hi there!
I have been trying to have a cell not allow a certain character if another cell already has counted 10 of that group of characters in that column.
For example, cell B2 has a sumproduct function that counts up a total of all "E", "p", "Q", and "O" entries in column 2 (about 100 rows in the column). I want to be able to stop someone from entering another E, p, Q, or O in column 2 if cell B2 already has a total of 10. I want a box to pop up and say something like "This shift is full, choose another". I've been playing around with it, but I can't get it to do that. Any ideas?

Column B

=sumproduct(--exact(B4:B104,{"E","O","Q","p"}))

E
O
E
Q
E
E
p

etc.
Thanks Slink

 

[Andrew Poulsom]

Re: Data validation referring to cell that has count if func

The validation formula:

=B2<=10

should work. You can set the error message on the Error Alert tab.

 

[SlinkRN]

Re: Data validation referring to cell that has count if func

I thought that would work too, but it doesn't. I thought maybe it was because it was based on a sumproduct formula instead of an actual number.

 

[Andrew Poulsom]

Re: Data validation referring to cell that has count if func

You probably need:

=$B$2<=10

if it's applied to a range. It worked for me.

 

[SlinkRN]

Re: Data validation referring to cell that has count if func

Ooops! Sorry, Andrew - I spoke too soon. That DOES work - I just tried it. I must not have been putting the = before the B2. Thank you so much, the simplest little thing was driving me CRAZY!! Thanks again! Slink