Smartsheet formula to only apply to Parent Tasks with specific criteria

alagator28
alagator28 ✭✭
edited 03/14/23 in Formulas and Functions

Hello,

I have been using Smartsheet for a few months but am definitely still considered a beginner. So far, I've gotten by fairly well thanks to this forum :)

I am using the following formula which calculates the % of child tasks which are Complete, and it only applies to Parent tasks. This is great, but I only want it to show on Parent tasks which are NOT Cancelled or Completed. The logic here is that I may mark an entire Parent task as Complete without worrying about all child task statuses.

Can you help me add the criteria required to get this done? Here is what I have which works:


=IF(COUNT(CHILDREN([Parent Tasks]@row)) > 0, (IF(COUNT(CHILDREN([Parent Tasks]@row)) > 0, (COUNTIF(CHILDREN(Status@row), Status@row = "Complete"))) / ((IF(COUNT(CHILDREN([Parent Tasks]@row)) > 0, COUNT(CHILDREN(Status@row)))) - (IF(COUNT(CHILDREN([Parent Tasks]@row)) > 0, (COUNTIF(CHILDREN(Status@row), Status@row = "Cancelled"))))))


Thanks!

Tags:

Best Answer

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    Hi @alagator28

    You can add an IF statement at the very beginning of the formula that checks the Status of the current Parent row before going into your percent formula.

    Try something like this:

    =IF(OR(Status@row = "Cancelled", Status@row = "Complete"), "", IF(COUNT(CHILDREN([Parent Tasks]@row)) > 0, (IF(COUNT(CHILDREN([Parent Tasks]@row)) > 0, (COUNTIF(CHILDREN(Status@row), Status@row = "Complete"))) / ((IF(COUNT(CHILDREN([Parent Tasks]@row)) > 0, COUNT(CHILDREN(Status@row)))) - (IF(COUNT(CHILDREN([Parent Tasks]@row)) > 0, (COUNTIF(CHILDREN(Status@row), Status@row = "Cancelled")))))))


    I've made it return "blank", but you could adjust this to 100% if you'd like:

    =IF(OR(Status@row = "Cancelled", Status@row = "Complete"), 1, IF(COUNT(CHILDREN([Parent Tasks]@row)) > 0, (IF(COUNT(CHILDREN([Parent Tasks]@row)) > 0, (COUNTIF(CHILDREN(Status@row), Status@row = "Complete"))) / ((IF(COUNT(CHILDREN([Parent Tasks]@row)) > 0, COUNT(CHILDREN(Status@row)))) - (IF(COUNT(CHILDREN([Parent Tasks]@row)) > 0, (COUNTIF(CHILDREN(Status@row), Status@row = "Cancelled")))))))


    Let me know if that helps!

    Cheers,

    Genevieve

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

Answers

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    Hi @alagator28

    You can add an IF statement at the very beginning of the formula that checks the Status of the current Parent row before going into your percent formula.

    Try something like this:

    =IF(OR(Status@row = "Cancelled", Status@row = "Complete"), "", IF(COUNT(CHILDREN([Parent Tasks]@row)) > 0, (IF(COUNT(CHILDREN([Parent Tasks]@row)) > 0, (COUNTIF(CHILDREN(Status@row), Status@row = "Complete"))) / ((IF(COUNT(CHILDREN([Parent Tasks]@row)) > 0, COUNT(CHILDREN(Status@row)))) - (IF(COUNT(CHILDREN([Parent Tasks]@row)) > 0, (COUNTIF(CHILDREN(Status@row), Status@row = "Cancelled")))))))


    I've made it return "blank", but you could adjust this to 100% if you'd like:

    =IF(OR(Status@row = "Cancelled", Status@row = "Complete"), 1, IF(COUNT(CHILDREN([Parent Tasks]@row)) > 0, (IF(COUNT(CHILDREN([Parent Tasks]@row)) > 0, (COUNTIF(CHILDREN(Status@row), Status@row = "Complete"))) / ((IF(COUNT(CHILDREN([Parent Tasks]@row)) > 0, COUNT(CHILDREN(Status@row)))) - (IF(COUNT(CHILDREN([Parent Tasks]@row)) > 0, (COUNTIF(CHILDREN(Status@row), Status@row = "Cancelled")))))))


    Let me know if that helps!

    Cheers,

    Genevieve

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

  • Thank you, @Genevieve P. ! Apologies for the delayed response.

    I was able to get it to work, thanks to your answer. I made some slight modifications, but I'm happy with how it works now. I'm basically only showing the % of sub-tasks complete if the status is In Progress.

    Here is what I ended up with.

    =IF((Status@row = "In Progress"), IF(COUNT(CHILDREN([Parent Tasks]@row)) > 0, (IF(COUNT(CHILDREN([Parent Tasks]@row)) > 0, (COUNTIF(CHILDREN(Status@row), Status@row = "Complete"))) / ((IF(COUNT(CHILDREN([Parent Tasks]@row)) > 0, COUNT(CHILDREN(Tasks@row)))) - (IF(COUNT(CHILDREN([Parent Tasks]@row)) > 0, (COUNTIF(CHILDREN(Status@row), Status@row = "Cancelled"))))))))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!