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
 10.6K Get Help
 63 Global Discussions
 67 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 55 Brandfolder
 125 Just for fun
 50 Community Job Board
 464 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!