Parent/child roll up formula
Does anyone have feedback on the formula below? I may have some parentheses where there shouldn't be. Here is what I am trying to accomplish in the parent cell:
"Complete", if all children are "Complete"
Leave the cell blank, if all children are blank
"On Hold", if any children are "On Hold"
"Requirements Needed to Begin", if any children are "Requirements Needed to Begin"
"In Progress", if any children are "In Progress"
=IF(COUNT(CHILDREN()) = COUNTIFS(CHILDREN(), "Complete"), "Complete"), IF(COUNTIFS(CHILDREN(), 0), 0), IF(COUNTIFS(CHILDREN(), "On Hold") > 0, "On Hold"), IF(COUNTIFS(CHILDREN(), "Requirements Needed to Begin") > 0, "Requirements Needed to Begin"), IF(COUNTIFS(CHILDREN(), "In Progress") > 0, "In Progress")
Thank you!
Answers

It looks like you have some misplaced parenthesis.
=IF(COUNT(CHILDREN()) = COUNTIFS(CHILDREN(), "Complete"), "Complete", IF(COUNTIFS(CHILDREN(), ""), "", IF(COUNTIFS(CHILDREN(), "On Hold") > 0, "On Hold", IF(COUNTIFS(CHILDREN(), "Requirements Needed to Begin") > 0, "Requirements Needed to Begin", IF(COUNTIFS(CHILDREN(), "In Progress") > 0, "In Progress")))))
Help Article Resources
Categories
Check out the Formula Handbook template!