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 :thumbup: 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


    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 :thumbup: 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 :thumbup: 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.