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

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)
- 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:
- 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!
Answers
-
***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"
Help Article Resources
Categories
Check out the Formula Handbook template!