Smartsheet formula to only apply to Parent Tasks with specific criteria

alagator28

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!

Genevieve P.

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

Genevieve P.

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

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"))))))))