Hi There! I'm hoping I'll eventually have time to take the formulas training that smartsheet offers but in the meantime, can anyone help me write a formula that's above my pay grade? 😁

I have a formula for calculating our overall progress for a project. However, I've had to break the project down into tasks that, while they need to be completed, don't actually contribute to the overall implementation progress (retrospectives, post production support, additional post launch training, etc).

So, my current progress column has this formula:

=IFERROR(SUM(CHILDREN(Progress@row)) / COUNT(CHILDREN(Status@row)), IF(Status@row = "In Progress", "", IF(Status@row = "Completed", 1)))

I would like to add another condition, where, if the column named "Phase" contains "Prod Support", ignore it or don't count it towards the overall project.

I feel like IF(NOT(CONTAINS("Prod Support", Phase@row))) needs to be nested somewhere but I can't seem to get it right.

Can anyone assist?




  • Stefan
    Stefan ✭✭✭✭✭✭

    Hi @jess_roberts,

    try this:

    =IFERROR(SUM(CHILDREN(Progress@row)) / COUNT(CHILDREN(Status@row)), IF(Status@row = "In Progress", "", IF(AND(Status@row = "Completed", Phase@row <> "Prod Support"), 1)))

    BUT, if "Prod Support" is not part of you progress idea, then you should also take those tasks out of your SUM(CHILDREN) statement. So try this:

    =IFERROR(SUMIF(CHILDREN(Progress@row), <>"", CHILDREN(Progress@row)) / COUNTIF(CHILDREN(Phase@row), <>"Prod Support"), IF(OR(Status@row = "In Progress", Phase@row = "Prod Support"), 0, IF(AND(Status@row = "Completed", Phase@row <> "Prod Support"), 1)))

    Hope this helps


    Smartsheet Consulting, Solution Building, Training and Support.

    Projects for Processes and for People.

  • Thanks for the response! The "Phase" with prod support will not be static "Prod Support" It's going to be Phase x Prod Support for each phase. It's variable. That's why I was hoping to be able to use "Contains" instead of =. Can we do that?

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    This may work:

    =IFERROR(SUMIFS(CHILDREN(Progress@row), CHILDREN(Progress@row), @cell <>"", CHILDREN(Phase@row), NOT(CONTAINS("Product Support", @cell))) / COUNTIF(CHILDREN(Phase@row), NOT(CONTAINS("Product Support", @cell))), IF(OR(Status@row = "In Progress", Phase@row = "Prod Support"), 0, IF(AND(Status@row = "Completed", NOT(CONTAINS("Product Support", Phase@row))), 1)))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!