Multiple ifs when looking at child tasks

Karve ✭✭
edited 09/22/23 in Formulas and Functions

Hi All

I've got this formula which checks if a line has children then if any of them are "RAG" Green it prints a 1. I am using it to create a report with nested line items

=IF(COUNT(CHILDREN([Task Name]@row)) > 0, IF(COUNTIFS(DESCENDANTS(RAG@row), "Green") > 0, 1))

I am trying to limit returning "1" only if any of the children have a "Finish" date less than 7 days in the future by adding to this something like

=IF([Finish]@row < TODAY(+7))

but im struggling to get these 2 to work together with the correct syntax.

Any help is appreciated.

Best Answer

  • Karve
    Karve ✭✭
    Answer ✓

    Thanks for the direction Paul

    =IF(COUNT(CHILDREN([Task Name]@row)) > 0, IF(COUNTIFS(DESCENDANTS(RAG@row), "Green", DESCENDANTS(Finish@row), <TODAY(+7)) > 0, 1))

    Gave me what I needed


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!