Combining an IF formula to returning different values based on row level


Hello lovely Smartsheet community.

I have a status column using symbols.

The status is set using a formula, but I currently have two different formulas depending on the level of the row.

If the row is a second level I have the following formula:

=IF(COUNTIFS(CHILDREN(), "Red") = COUNT(CHILDREN()), "Red", IF(COUNTIFS(CHILDREN(), "Green") = COUNT(CHILDREN()), "Green", IF([Task Not Required this Year]2 = true, "Blue", IF(Progress3 = "Quarter", "Yellow", IF(Progress2 = "Half", "Yellow", IF(Progress2 = "Three Quarter", "Yellow", IF(Progress2 = "Full", "Green", "")))))))

If the row is anything below second level, then I have this formula:

=IF([Task Not Required this Year]81 = true, "Blue", IF(Progress81 = "", "", IF(Progress81 = "Quarter", "Yellow", IF(Progress81 = "Half", "Yellow", IF(Progress81 = "Three Quarter", "Yellow", IF(Progress81 = "Full", "Green", "Red"))))))

Both work fine independently but I am struggling to combine the formula so that I just have one, allowing easier insertion of rows without the need to consider which formula should be used.

Help would be most welcome.

Many thanks



  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi @Natalie Gorman

    I hope you're well and safe!

    Try adding something like this.

    I usually add a so-called helper column called Level with the formula below, and then you can use it to identify parents and use it for conditional formatting and more. (you can also include it in your formula, depending on what levels you need to check for)

    =IF(COUNT(CHILDREN([Task Name]@row)) > 0, COUNT(ANCESTORS()) + 1)

    Depending on your country/region, you'll need to exchange the comma to a period and the semi-colon to a comma.

    Would that work/help?

    I hope that helps!

    Be safe and have a fantastic week!


    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!


    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: | | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Natalie Gorman
    Natalie Gorman ✭✭✭✭

    Thank you @Andrée Starå I will give that a go.

    I have another formula question. I currently have the following:

    =IF(COUNTIFS(CHILDREN(), "Red") = COUNT(CHILDREN()), "Red", IF(COUNTIFS(CHILDREN(), "Green") = COUNT(CHILDREN()), "Green", "Yellow"))

    I want to add in an or at the start which is saying, if red or blue, then red. The rest of the formula remains the same.

    I have tried multiple different ways and I can't seem to get the formula right.

    Many thanks


  • Natalie Gorman
    Natalie Gorman ✭✭✭✭

    Hi @Andrée Starå

    I have put in the helper column as suggested but still struggling to combine the two.

    I get the following to work on the first row

    =IF(AND(Helper@row = 2, (COUNTIFS(CHILDREN(), "Red") = COUNT(CHILDREN()))), "Red", IF(AND(Helper@row = 2, (COUNTIFS(CHILDREN(), "Blue") = COUNT(CHILDREN()))), "Blue", IF(AND(Helper@row = 2, (COUNTIFS(CHILDREN(), "Yellow") = COUNT(CHILDREN()))), "Yellow", IF(AND(Helper@row = 2, (COUNTIFS(CHILDREN(), "Green") = COUNT(CHILDREN()))), "Green", IF(AND(Helper@row <> 2, IF([Task Not Required this Year]@row = true, "Blue", IF(AND(Helper@row <> 2, IF(Progress@row = "", "", IF(AND(Helper@row <> 2, IF(Progress@row = "Quarter", "Yellow", IF(AND(Helper@row <> 2, IF(Progress@row = "Half", "Yellow", IF(AND(Helper@row <> 2, IF(Progress@row = "Three Quarter", "Yellow", IF(AND(Helper@row <> 2, IF(Progress@row = "Full", "Green", "Red"))))))))))))))))))))))

    However, when when I drag the formula down I get one of two errors on all rows including the one that was working:

    #blocked or #incorrect argument set. The latter appears to occur on rows where the helper column is not returning a value as there are no children.

    I'm sure I am missing something obvious but I am unable to see what it is.

    Help would be much appreciated.

    Kind regards,


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!