I have created a sample sheet at

Ultimately, I want to use this formula in a data validation to ensure the same ID is not added twice. As such, I don't think I can use ARRAYFORMULA.

CodePudding user response：

Append apostrophe "'" before the cell value in the 2nd parameter of =COUNTIF(). The apostrophe indicates that the cell is formatted to text and force functions to recognize it as a text only.

=COUNTIF(A2:A, "'"&A2)

Example:

CodePudding user response：

As already answered here: