Question on status rollup using IF COUNTIF formula

larak

I have 4 statuses (Not Started, In Progress, Complete and N/A). I really want the N/A to either not be considered in the rollup, or to be treated like 'Complete.' No matter what I do, I keep getting an unparseable error. This is the latest version of what I have:

=IF(COUNTIF(CHILDREN(), "Complete") = COUNT(CHILDREN()), "Complete", IF(COUNTIF(CHILDREN(), "Not Started") = COUNT(CHILDREN()), "Not Started", IF(COUNTIF(CHILDREN(), "In Progress") > 0, "In Progress", IF(COUNTIF(CHILDREN(), "Not Started") > 0, "In Progress", IF(COUNTIF(CHILDREN(), "N/A") > 0, "Complete", )))))

Any suggestions on how to get this to work?

Paul Newcome

The initial issue is that the comma after "Complete" at the end needs removed.

You could also have it count the same as "Complete" by using an OR function like so:

=IF(COUNTIF(CHILDREN(), OR(@cell = "Complete", @cell = "N/A")) = COUNT(CHILDREN()), "Complete", IF(COUNTIF(CHILDREN(), "Not Started") = COUNT(CHILDREN()), "Not Started", "In Progress"))

Paul Newcome

The initial issue is that the comma after "Complete" at the end needs removed.

You could also have it count the same as "Complete" by using an OR function like so:

=IF(COUNTIF(CHILDREN(), OR(@cell = "Complete", @cell = "N/A")) = COUNT(CHILDREN()), "Complete", IF(COUNTIF(CHILDREN(), "Not Started") = COUNT(CHILDREN()), "Not Started", "In Progress"))

larak

Thank you @Paul Newcome !

Paul Newcome

Happy to help. 👍️