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
Natalie
Answers

I hope you're well and safe!
Try adding something like this.
I usually add a socalled 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 semicolon to a comma.
Would that work/help?
I hope that helps!
Be safe and have a fantastic week!
Best,
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!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå  Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com  E:andree@workbold.com  P: +46 (0)  72  510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

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

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,
Natalie
Help Article Resources
Categories
Check out the Formula Handbook template!