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"