Checkbox find duplicate in a column, do not check either box if either has a value in 2nd column


I am using a checkbox to find duplicate entries in the column name Email. I can up with this that worked, =IF(COUNTIF(Email:Email, Email@row) > 1, 1). 

Next, I am looking for both duplicates to not have a value in the Using Generic Account field and then check both boxes. Do not check either box if one of the two duplicates has a value in the Using Generic Account field. I can up with this =IFERROR(AND(IF(COUNTIF(Email:Email, Email@row) > 1, 1), IF(COUNTIF([Using Generic Account]:[Using Generic Account], [Using Generic Account]@row) > 1, 1)), 0). 

This works if both rows do not have a value in the Using Generic Account field. But if one does, the one without the value is still checked. 

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    I want to make sure I understand...

    You have two columns. Email and [Using Generic Account]. You want to only flag duplicates that are blank in the [Using Generic Account] for all entries. If there is no duplicate or even one of the duplicates has data in the [Using Generic Account] column then you want to leave the boxes unchecked?

    If the above is correct, try this:

    =IF(AND(COUNTIFS(Email:Email, @cell = Email@row)> 1, COUNTIFS(Email:Email, @cell = Email@row, [Using Generic Account]:[Using Generic Account], @cell <> "") = 0), 1)


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!