Help with Roll Up formula

TrialUser

Please help! I have a formula in my status column that works with the exception of one scenario.

If I have say 5 children and of those, 4 with a status left blank and 1 that is "Complete", it is rolling up to "In Progress" as I need it to. When it changes to more than 1 "Complete", it rolls up to "Complete." If there are any number of "Complete" status children but even just 1 blank status, I need it to Roll up to "In Progress."

I have tried switching this around a million different ways and just can't figure it out.

Here is my formula:

=IF(COUNTIF(CHILDREN(), " ") + COUNTIF(CHILDREN(), "N/A") = COUNT(CHILDREN()), "Not Started",

IF(COUNTIF(CHILDREN(), "Complete") + COUNTIF(CHILDREN(), "") > 0 = COUNT(CHILDREN()), "In Progress",

IF(COUNTIF(CHILDREN(), "Complete") + COUNTIF(CHILDREN(), "N/A") = COUNT(CHILDREN()), "Complete",

IF(COUNTIF(CHILDREN(), "In Progress") = COUNT(CHILDREN()), "In Progress",

IF(COUNTIF(CHILDREN(), "Complete") = COUNT(CHILDREN()), "Complete",

IF(COUNTIF(CHILDREN(), "N/A") = COUNT(CHILDREN()), "N/A",

IF(COUNTIF(CHILDREN(), "Off Track") > 0, "Off Track",

IF(COUNTIF(CHILDREN(), "At Risk") > 0, "At Risk",

IF(COUNTIF(CHILDREN(), "In Progress") > 0, "In Progress",

IF(COUNTIF(CHILDREN(), "On Hold") > 0, "On Hold", "In Progress"))))))))))

With 1 Complete:

With more than 1 Complete:

I appreciate any help you can offer!

Jon Mark H

Hey!

This MAY be an easy fix -

One little quirk you'll run into is in referencing children of a column where some items are blank - it doesn't count it as a child if there's nothing there - despite the row being indented.

I would work around this by referencing your primary column as the baseline comparison to your Countifs -

First portion of your formula:

=IF(COUNTIF(CHILDREN(), " ") + COUNTIF(CHILDREN(), "N/A") = COUNT(CHILDREN([Primary Column])), "Not Started",

IF(COUNTIF(CHILDREN(), "Complete") + COUNTIF(CHILDREN(), "") > 0 = COUNT(CHILDREN([Primary Column])), "In Progress",

IF(COUNTIF(CHILDREN(), "Complete") + COUNTIF(CHILDREN(), "N/A") = COUNT(CHILDREN([Primary Column])), "Complete",

Substituting [Primary Column] with the name of your primary column - might be [Sales Activities]

Let me know if that fixes your issue or not - I'd love to try to help more if it doesn't!

-Jon Mark