Help with IF formula checking something in 25 different ranges.

CNB_Chris_Hallo

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!

Paul H

If I understand what you are asking I think this would work

=IF(COUNTIF(Role:Role, Role@row) = 1, "Y", "N")

CNB_Chris_Hallo

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 :).

CNB_Chris_Hallo

@Paul Newcome , I came across a post you helped with that checked dates in multiple ranges. Any insight on this one by chance?

Paul Newcome

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.

CNB_Chris_Hallo

@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.

Paul Newcome

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")