Formula to check duplicates works...but needs to exclude blanks
Good morning! I'm hoping another perspective will point out the obvious here for me :).
The formula is working correctly in Test_A2 if the Role is the same within the given range. Where I'm having a problem is how/where to add in isblank so that Test_A2 equals N if the Role is blank within the range. Ideally, I'd like to keep this in one column without the need for an additional helper but am not opposed if its the best way.
Best Answers

I think it should look more like this
=IF(OR(CONTAINS(Role@row, Role30:Role39), Contains(Role@row, Role54:Role63)),"N", "Y")

@Paul H , MUCH appreciated :).

Greetings, @Paul H and Community! I could use some help with an IF formula  please :).
Background: There are years (0 through 5) that have processes (1 through 25) and each process has roles (1 through 10). In the attached image, I'm adding a formula in the Test_A1 that should check within all roles/processes per year to determine if it is unique (used only once) within the entire year.
I do already have an Excel file with the ranges so want to ensure:
 the syntax asis would check the first two ranges
 How would I modify it to add each additional range? An example would be great!
Answers

Nest another IF statement
=IFERROR(IF(ISBLANK(Role@row),"N",IF(CountIf(role30..................................),"")

Thanks, @Paul H. I entered the below and am getting an incorrect argument set error. Is the syntax off?
=IFERROR(IF(ISBLANK(Role@row), "N", IF(COUNTIFS(Role30:Role39, Role@row) > 1, "Y", "N"), ""))

Move the last ,""
=IFERROR(IF(ISBLANK(Role@row), "N", IF(COUNTIFS(Role30:Role39, Role@row) > 1, "Y", "N")),"")

@Paul H, that worked great (in Test_A0), thank you! For Test_A01, I would like to add a formula that checks the range for process 1 as well as process 2 (there are 25 processes total so I'm thinking a long OR with the ranges for each). For that, I tried the below and get an error. Any insight there?
=if(OR(Role30:Role39,Contains(Role@row)OR(Role54:Role63,Contains(Role@row),"N","Y")))

I think it should look more like this
=IF(OR(CONTAINS(Role@row, Role30:Role39), Contains(Role@row, Role54:Role63)),"N", "Y")

@Paul H , MUCH appreciated :).

Greetings, @Paul H and Community! I could use some help with an IF formula  please :).
Background: There are years (0 through 5) that have processes (1 through 25) and each process has roles (1 through 10). In the attached image, I'm adding a formula in the Test_A1 that should check within all roles/processes per year to determine if it is unique (used only once) within the entire year.
I do already have an Excel file with the ranges so want to ensure:
 the syntax asis would check the first two ranges
 How would I modify it to add each additional range? An example would be great!
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 61.2K Get Help
 321 Global Discussions
 197 Industry Talk
 415 Announcements
 4.2K Ideas & Feature Requests
 126 Brandfolder
 153 Just for fun
 124 Community Job Board
 441 Show & Tell
 26 Member Spotlight
 1 SmartStories
 277 Events
 34 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!