Help with COUNTIF(S) formula to check for duplicates that meet multiple status criteria, and columns

N_K

Hi, I'm still new with Smartsheet and even newer with figuring out the syntax of formulas, and was wondering if someone can help me with a request

Currently I have a COUNTIF formula to check a box if there is a duplicate number in the ID Number Column:

=IF(COUNTIF([ID Number]:{ID Number], [ID Number@row)>1,1,0)

1. How do I write a formula to check off duplicate only if the Status (dropdown list) is "Approved" or "Under Revision" and if it doesn't have that status the box stays unchecked and if possible:
2. If that ID number were to change, is there a way to incorporate the Legacy ID Number Column into the duplicate check with the status criteria as well.

For instance say they want to know that the old number was a duplicate, (so you're counting the Legacy ID Number column against the ID Number Column, but also maintain checking for duplicates in the ID Number column, so if there were multiple versions of "NEW-TEST-42100" and it met the status criteria it would check the duplicate box.

Any help would be appreciated, Thanks!

N_K

***EDIT Never mind it didn't work as I intended

Ok I was able to figure out the second half of my question, how do I factor in status of "Approved" and "Under Revision"?

using this formula: =IF(COUNTIF([Legacy ID Number]:[ID Number], [ID Number]@row) > 1, 1, 0)

I get this result below: All I need now is for the duplicate box to Uncheck when the status isn't one "Approved" or "Under Revision"

Nick Korna

Hi @N_K,

Use a nested IF statement:

=IF(AND(Status@row <> "Approved", Status@row <> "Under Revision"), 0, IF(COUNTIF([Legacy ID Number]:[ID number], [ID number]@row) > 1, 1, 0))

If the Status isn't Approved/Under Revision, the box is unticked, if it is one of those then your previous formula will be in effect to check for duplicates.

Hope this helps, if there are any problems/questions then just post! 🙂