Smartsheet formula to only apply to Parent Tasks with specific criteria

Options
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 Admin
    Answer ✓
    Options

    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

Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓
    Options

    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

  • alagator28
    Options

    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!