Parent Row Roll Up

ajcooper35 · 01/03/19

I am looking to create a formula that will do the following:

If all child statuses = Complete, it will say "Complete"

If at least one child says in progress or complete, the parent row will say "In Progress"

If all child statuses say Not Started, or are Blank, it will say "Not Started"

I tried using this as a shell:

=IF(COUNTIF(CHILDREN(), "Not Started") > 0, "Not Started", IF(COUNTIF(CHILDREN(), "In Progress") > 0, "In Progress", IF(COUNTIF(CHILDREN(), "Complete") > 0, "Complete", "Verify")))

But it is making the whole row say Complete is only one if complete.

Anyone have any ideas?

Paul Newcome · 01/03/19

You're on the right track. Try something like this...

=IF(COUNTIFS(CHILDREN(), "Complete") = COUNT(CHILDREN([Task Column]@row)), "Complete", IF(COUNTIFS(CHILDREN(), OR(@cell = "Not Started", ISBLANK(@cell)) = COUNT(CHILDREN([Task Column]@row)), "Not Started", IF(COUNTIFS(CHILDREN(), "In Progress") > 0, "In Progress", "Verify")))

ajcooper35 · 01/04/19

I ended up going with this... definitely not the most 'right' way... but it worked.

I tried the above one and it gave me 'unparseable'

=IF(AND(COUNTIF(CHILDREN(), "") = 0, COUNTIF(CHILDREN(), "Not Started") = 0, COUNTIF(CHILDREN(), "In Progress") = 0, COUNTIF(CHILDREN(), "Complete") = 0), "Not Started", IF(AND(COUNTIF(CHILDREN(), "") = 0, COUNTIF(CHILDREN(), "Not Started") > 0, COUNTIF(CHILDREN(), "In Progress") = 0, COUNTIF(CHILDREN(), "Complete") = 0), "Not Started", IF(AND(COUNTIF(CHILDREN(), "") > 0, COUNTIF(CHILDREN(), "Not Started") > 0, COUNTIF(CHILDREN(), "In Progress") = 0, COUNTIF(CHILDREN(), "Complete") = 0), "Not Started", IF(AND(COUNTIF(CHILDREN(), "") > 0, COUNTIF(CHILDREN(), "Not Started") = 0, COUNTIF(CHILDREN(), "In Progress") = 0, COUNTIF(CHILDREN(), "Complete") = 0), "Not Started", IF(AND(COUNTIF(CHILDREN(), "") = 0, COUNTIF(CHILDREN(), "Not Started") = 0, COUNTIF(CHILDREN(), "In Progress") > 0, COUNTIF(CHILDREN(), "Complete") = 0), "In Progress", IF(AND(COUNTIF(CHILDREN(), "") > 0, COUNTIF(CHILDREN(), "Not Started") = 0, COUNTIF(CHILDREN(), "In Progress") > 0, COUNTIF(CHILDREN(), "Complete") = 0), "In Progress", IF(AND(COUNTIF(CHILDREN(), "") > 0, COUNTIF(CHILDREN(), "Not Started") > 0, COUNTIF(CHILDREN(), "In Progress") > 0, COUNTIF(CHILDREN(), "Complete") = 0), "In Progress", IF(AND(COUNTIF(CHILDREN(), "") > 0, COUNTIF(CHILDREN(), "Not Started") > 0, COUNTIF(CHILDREN(), "In Progress") > 0, COUNTIF(CHILDREN(), "Complete") > 0), "In Progress", IF(AND(COUNTIF(CHILDREN(), "") = 0, COUNTIF(CHILDREN(), "Not Started") > 0, COUNTIF(CHILDREN(), "In Progress") > 0, COUNTIF(CHILDREN(), "Complete") = 0), "In Progress", IF(AND(COUNTIF(CHILDREN(), "") = 0, COUNTIF(CHILDREN(), "Not Started") > 0, COUNTIF(CHILDREN(), "In Progress") > 0, COUNTIF(CHILDREN(), "Complete") > 0), "In Progress", IF(AND(COUNTIF(CHILDREN(), "") = 0, COUNTIF(CHILDREN(), "Not Started") = 0, COUNTIF(CHILDREN(), "In Progress") > 0, COUNTIF(CHILDREN(), "Complete") > 0), "In Progress", IF(AND(COUNTIF(CHILDREN(), "") > 0, COUNTIF(CHILDREN(), "Not Started") = 0, COUNTIF(CHILDREN(), "In Progress") = 0, COUNTIF(CHILDREN(), "Complete") > 0), "In Progress", IF(AND(COUNTIF(CHILDREN(), "") > 0, COUNTIF(CHILDREN(), "Not Started") = 0, COUNTIF(CHILDREN(), "In Progress") > 0, COUNTIF(CHILDREN(), "Complete") > 0), "In Progress", IF(AND(COUNTIF(CHILDREN(), "") > 0, COUNTIF(CHILDREN(), "Not Started") > 0, COUNTIF(CHILDREN(), "In Progress") = 0, COUNTIF(CHILDREN(), "Complete") > 0), "In Progress", IF(AND(COUNTIF(CHILDREN(), "") = 0, COUNTIF(CHILDREN(), "Not Started") = 0, COUNTIF(CHILDREN(), "In Progress") = 0, COUNTIF(CHILDREN(), "Complete") > 0), "Complete")))))))))))))))

Parent Row Roll Up

Comments

Categories