Help with IF formula checking something in 25 different ranges.
Greetings! There are 25 different ranges I'm trying to check to determine if a role is unique within all 25 groups (each group has 10 roles each).
The formula is the highlighted cell currently showing #UNPARSABLE is this:
=if($Role$30:$Role$39(or($Role$54:$Role$63(or($Role$78:$Role$87(or($Role$102:$Role$111(or($Role$126:$Role$135(or($Role$150:$Role$159(or($Role$174:$Role$183(or($Role$198:$Role$207(or($Role$222:$Role$231(or($Role$246:$Role$255(or($Role$271:$Role$280(or($Role$295:$Role$304(or($Role$319:$Role$328(or($Role$343:$Role$352(or($Role$367:$Role$376(or($Role$392:$Role$401(or($Role$416:$Role$425(or($Role$440:$Role$449(or($Role$464:$Role$473(or($Role$488:$Role$497(or($Role$513:$Role$522(or($Role$537:$Role$546(or($Role$561:$Role$570(or($Role$585:$Role$594(or($Role$609:$Role$618
I think I may have gotten lost with all the ranges but what I'm trying to accomplish is if the role is unique that Y though if the role repeats within the 25 roles then N.
Thanks, Community!
Answers
-
If I understand what you are asking I think this would work
=IF(COUNTIF(Role:Role, Role@row) = 1, "Y", "N")
-
Hello, @Paul H. This would work if the role were used anywhere within the column but I need it to check each of the ranges. Using your IF, what would the syntax be to paste in the various ranges?
Hierarchy is currently setup as Year ( 0 through 5) and each year has 25 processes with 10 rows each. The formula should check all roles/processes within the year. Once I have the syntax for year 0, I'll replace in Year 1 through 5 with the ranges for those years.
Thank you for your efforts :).
-
@Paul Newcome , I came across a post you helped with that checked dates in multiple ranges. Any insight on this one by chance?
-
Try something along the lines of...
=IF(COUNTIFS(DESCENDANTS(Role1), @cell = Role@row)> 1, "N", "Y")
Just change Role1 to whatever row the year starts.
If you wanted to have something such as a master list and a single formula to check each, then it would look something more like this:
=IF(OR(COUNTIFS(DESCENDANTS(Role1), @cell = Role@row)> 1, COUNTIFS(DESCENDANTS(Role97), @cell = Role@row)> 1, COUNTIFS(DESCENDANTS(Role184), @cell = Role@row)> 1), "N", "Y")
Again... Just change the row numbers after the initial Role reference to match the rows that the year numbers are on.
There are a number of other ways to accomplish this if I am understanding your needs correctly, but the above is going to be the most straightforward without creating additional columns.
-
@Paul Newcome, first thank you for even replying :). I'm thinking option 2 and plugged in row 1 of 10 for the 25 processes of year 1:
=IF(OR(COUNTIFS(DESCENDANTS(Role30), @cell = Role@row) > 1, COUNTIFS(DESCENDANTS(Role54), @cell = Role@row) > 1, COUNTIFS(DESCENDANTS(Role78), @cell = Role@row) > 1, COUNTIFS(DESCENDANTS(Role102), @cell = Role@row) > 1, COUNTIFS(DESCENDANTS(Role126), @cell = Role@row) > 1, COUNTIFS(DESCENDANTS(Role150), @cell = Role@row) > 1, COUNTIFS(DESCENDANTS(Role174), @cell = Role@row) > 1, COUNTIFS(DESCENDANTS(Role198), @cell = Role@row) > 1, COUNTIFS(DESCENDANTS(Role222), @cell = Role@row) > 1, COUNTIFS(DESCENDANTS(Role246), @cell = Role@row) > 1, COUNTIFS(DESCENDANTS(Role271), @cell = Role@row) > 1, COUNTIFS(DESCENDANTS(Role295), @cell = Role@row) > 1, COUNTIFS(DESCENDANTS(Role319), @cell = Role@row) > 1, COUNTIFS(DESCENDANTS(Role343), @cell = Role@row) > 1, COUNTIFS(DESCENDANTS(Role367), @cell = Role@row) > 1, COUNTIFS(DESCENDANTS(Role392), @cell = Role@row) > 1, COUNTIFS(DESCENDANTS(Role416), @cell = Role@row) > 1, COUNTIFS(DESCENDANTS(Role440), @cell = Role@row) > 1, COUNTIFS(DESCENDANTS(Role464), @cell = Role@row) > 1, COUNTIFS(DESCENDANTS(Role488), @cell = Role@row) > 1, COUNTIFS(DESCENDANTS(Role513), @cell = Role@row) > 1, COUNTIFS(DESCENDANTS(Role537), @cell = Role@row) > 1, COUNTIFS(DESCENDANTS(Role561), @cell = Role@row) > 1, COUNTIFS(DESCENDANTS(Role585), @cell = Role@row) > 1, COUNTIFS(DESCENDANTS(Role609), @cell = Role@row) > 1), "N", "Y")
Note that A and B (same role, different process both return Y. The intent is to see if the role is unique within all processes for a year so I know once this works for year 0, I'll need to do the same for years 1 through 5. Since the same role is used (A and B), what needs to change so that B returns N (as in, the role is not unique because it was used in A). I really hope that makes sense and that the visual helps.
-
I see now. In that case you will need to add the COUNTIFS together instead of nesting them in an OR function.
=IF(COUNTIFS(DESCENDANTS(Role30), @cell = Role@row) + COUNTIFS(DESCENDANTS(Role54), @cell = Role@row) + COUNTIFS(DESCENDANTS(Role78), @cell = Role@row) > 1, "N", "Y")
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!