Nested Formula IF(NOT(CONTAINS AND IFERROR(SUM(CHILDREN

jess_roberts

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?

Thanks!

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

Stefan

jess_roberts

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

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