Duplicate Formula Not Working As Expected


I have two columns, one has job numbers in it, and the one beside it has this formula to detect if the first coiumn has duplicates: =IF(COUNTIF([Complete Job Number]:[Complete Job Number], [Complete Job Number]51) > 1, 1).

The row number--51 in my example formula--changes for each row.

It doesn't turn on when I have duplicates, but in the empty cells (you only see one in the screenshot but there are more below it), it does come on. What am I missing here?

Thanks for the help. I appreciate it.


  • Try

    =IF([Complete Job Number]@row = "", 0, IF(COUNTIF([Complete Job Number]:[Complete Job Number], [Complete Job Number]@row) > 1, 1, 0))

    This way, if the cell is blank, the box will automatically be checked and if it is not blank, then it will process the equation. Let me know if you have any questions!

  • Well, that got rid of the false positives for blank cells, but the checkboxes still aren't coming on when there is a duplicate number in the "Complete Job Number" column.


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!