Count and Countif Formula adding 4th Statement

I have an existing formula:

=IF(COUNT(CHILDREN(Status@row)) = 0, " ", IF(COUNTIF(CHILDREN(Status@row), "In Progress") > 0, "In Progress", IF(COUNTIF(CHILDREN(Status@row), "Complete") = COUNT(CHILDREN(Status@row)), "Complete", IF(COUNTIF(CHILDREN(Status@row), "Not Started") + COUNTIF(CHILDREN(Status@row), " ") = COUNT(CHILDREN(Status@row)), "Not Started"))))

which works perfectly.

I want to add a statement to accommodate a "Canceled" project. I tried adding it below; however, it says it is not the right syntax.

=IF(COUNT(CHILDREN(Status@row)) = 0, " ", IF(COUNTIF(CHILDREN(Status@row), "In Progress") > 0, "In Progress", IF(COUNTIF(CHILDREN(Status@row), "Complete") = COUNT(CHILDREN(Status@row)), "Complete", IF(COUNTIF(CHILDREN(Status@row), “Canceled”) = COUNT(CHILDREN(Status@row)), “Canceled, IF(COUNTIF(CHILDREN(Status@row), "Not Started") + COUNTIF(CHILDREN(Status@row), " ") = COUNT(CHILDREN(Status@row)), "Not Started"))))

Does anyone have any thoughts on what might be wrong?

Best Answer

  • Carson Penticuff
    Carson Penticuff ✭✭✭✭✭✭
    Answer ✓

    See if this fixes it:

    =IF(COUNT(CHILDREN(Status@row)) = 0, " ", IF(COUNTIF(CHILDREN(Status@row), "In Progress") > 0, "In Progress", IF(COUNTIF(CHILDREN(Status@row), "Complete") = COUNT(CHILDREN(Status@row)), "Complete", IF(COUNTIF(CHILDREN(Status@row), "Canceled") = COUNT(CHILDREN(Status@row)), "Canceled", IF(COUNTIF(CHILDREN(Status@row), "Not Started") + COUNTIF(CHILDREN(Status@row), " ") = COUNT(CHILDREN(Status@row)), "Not Started")))))

    I found a few "incorrect versions" of quotation marks, one missing quote, and also one missing closing parenthesis at the very end of the formula. Let me know if this does not fix it.

Answers

  • Carson Penticuff
    Carson Penticuff ✭✭✭✭✭✭
    Answer ✓

    See if this fixes it:

    =IF(COUNT(CHILDREN(Status@row)) = 0, " ", IF(COUNTIF(CHILDREN(Status@row), "In Progress") > 0, "In Progress", IF(COUNTIF(CHILDREN(Status@row), "Complete") = COUNT(CHILDREN(Status@row)), "Complete", IF(COUNTIF(CHILDREN(Status@row), "Canceled") = COUNT(CHILDREN(Status@row)), "Canceled", IF(COUNTIF(CHILDREN(Status@row), "Not Started") + COUNTIF(CHILDREN(Status@row), " ") = COUNT(CHILDREN(Status@row)), "Not Started")))))

    I found a few "incorrect versions" of quotation marks, one missing quote, and also one missing closing parenthesis at the very end of the formula. Let me know if this does not fix it.

  • Carson

    That fixed it. I figured it was something simple.

    Thank you!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!