Count and Countif Formula adding 4th Statement

kcaudill1989

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?

Carson Penticuff

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 Penticuff

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.

kcaudill1989

Carson

That fixed it. I figured it was something simple.

Thank you!