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:


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
    Paul H ✭✭✭✭✭✭

    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?

  • Paul Newcome
    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.

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!