True count of Children(), not just non blanks

vanZylin

Doing project roll up and trying to use the formula below to roll up a complete status.

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

However, blank cells do not contribute to the COUNT(CHILDREN()), thus if there are three children and someone deletes all values in 3 cells or any non "Complete", then the formula returns "Complete" because all rows with values does in fact equal the count of children.

How can I return the count of children regardless of blank cells?

Paul Newcome

Instead of comparing to the count of children, you would compare to the count of children that are blank or not blank.

Change

COUNT(CHILDREN())

to

COUNTIFS(CHILDREN(), OR(@cell <> "", @cell = ""))

Paul Newcome

Instead of comparing to the count of children, you would compare to the count of children that are blank or not blank.

Change

COUNT(CHILDREN())

to

COUNTIFS(CHILDREN(), OR(@cell <> "", @cell = ""))

vanZylin

That did the trick. I also used the count from another column that could not be blank...

COUNT(CHILDREN(d@row))

Paul Newcome

Happy to help. 👍️

I have done that too, but I have also inadvertently created circular references doing that. Haha. That's why I have tried to get into the habit of keeping it as tight as I can.