I need help on a multiple IF statement formula

I'm struggling to think through the logic to allow for the required scenarios.

  1. If the row is a master row - leave no flag as not at risk
  2. If the row is a 1st level sub heading - leave no flag as not at risk
  3. If the task isn't started by the start date = at risk!
  4. If the project has started but the review date is in the past = at risk!
  5. If there is no date all all in either columns - leave no flag as not at risk


@Andrée Starå 🙏


  • James Keuning
    James Keuning ✭✭✭✭✭

    First, use this to identify your level: =COUNT(ANCESTORS(ID@row)) -- ID is the field, change this to whatever.

    Then I would create five checkbox columns:

    1. Level > 1
    2. Start Date NOT >= Today
    3. Target Due date... you get the point here
    4. review date passed
    5. the no date test (I am not sure I understand this one)

    Maybe I have some of these backwards, in which case, just use NOT to get it so your desirable outcome TRUE or FALSE is accurate.

    Then test the columns with your IF statements.The way I do it is I create a column for each IF statement, so that I can make sure that it works, and I use 999 in the FALSE position. For example:

    Column A: IF(Test1=TRUE,"True Result",999)

    and then in Column B: IF(Test2=TRUE,"True Result",999)

    And then if both of those work, I place the second statement in the "999" position of the first:

    IF(Test1=TRUE,"True Result",IF(Test2=TRUE,"True Result",999))

  • thank you very much @James Keuning with much perseverance last night I managed to arrive at a solution in the formula to avoid the extra columns.

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭

    Hi @Gavin Worsley

    Hope you are fine, i advise you to add a helper column call it "Level" and use the following formula to define the level of the task:

    =IF(COUNT(CHILDREN(task@row)) > 0, COUNT(ANCESTORS()) + 1)

    then you can use this level easily to check the at-risk status of your tasks

    PMP Certified


    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • thank you @Bassam Khalil you can't see it on the sheet in the image but I have managed to use this in the sheet to assist the formula. thank you

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

    Hi @Gavin Worsley

    I hope you're well and safe!

    I didn't get a message that you mentioned me in your post.

    Glad you got it working!

    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 support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!


    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.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!