Input the ROWS function into that cell using the first and last cells of that column, not including the column heading. After that, you will know how many unique values are in that column.

Prevent duplicates before they happen by combining a simple function with data validation.