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.

Tiffany Castro

Hey Question about this! I was able to get an accurate count of my child rows HOWEVER, now it is adding a 0 value in the empty cells when I want them to still appear empty, is there a modification I can do to my formula to fix this??

Paul Newcome

@Tiffany Castro The most straightforward way to do this would be to use an IF statement.

=IF(COUNTIFS(……….) <> 0, COUNTIFS(……………))