# Restrict Cell Entry Based On COUNTIF Formula

• I am trying to write some code that will count the number of occurrences in a column (B4:B123) of the text entered in a cell once the cell is exited. I would also like a message to be displayed if the number is greater than 8. I have tried the Worksheet_Change event in combination with CountIf but I can't get it quite right. Any help would be appreciated.

You can say "THANK YOU" for help received by clicking the icon in the bottom right corner of the helper's post.
Practice makes perfect. I am very far from perfect so I'm still practising.

• Re: Count number of occurrences on cell exit

If the cell being exited is A1, have you tried the formula =COUNTIF(B4:B123, A1)

• Re: Count number of occurrences on cell exit

Thank you, Mike, for your quick reply. I am entering data down Column B starting at Row 4. As I type the data in each cell and press Enter or the TAB key, I want to check all of Column B to get a count of how many times I entered that same data and post a message when the count is greater than 8.

You can say "THANK YOU" for help received by clicking the icon in the bottom right corner of the helper's post.
Practice makes perfect. I am very far from perfect so I'm still practising.

• Re: Count number of occurrences on cell exit

Assuming that you are starting at B4, select B4 and set data Validation to the formula

=(COUNTIF(\$B\$4:\$B4, \$B4)<8) with the errror message of your choice.

Copy that validation to the rest of the cells (below B4) in column B.

• Re: Count number of occurrences on cell exit

Wow! That was so simple and worked like a charm. Thank you so very much.

You can say "THANK YOU" for help received by clicking the icon in the bottom right corner of the helper's post.
Practice makes perfect. I am very far from perfect so I'm still practising.