Count the number of non blank child cells

Aliaksandr Ilyuk

Hi!

The sheet contains the following hierarchy:

There are formulas (COUNT(CHILDREN())) in the blue cells of the Missing study column. I need to count the total number of white (child) cells. In this particular case [10] should appear.

What the formula should look like?

Thank you in advance!

Paul Newcome

You should be able to use

=SUM(CHILDREN())

But I am not sure if that will work because of the brackets. Is there a reason you have the brackets around the numbers?

Paul Newcome

Ok. If you really want to keep the brackets, you will need to insert a helper column that can later be hidden and replicate your counts without the brackets. Then use

=SUM(CHILDREN([Helper Column]@row))

Paul Newcome

You should be able to use

=SUM(CHILDREN())

But I am not sure if that will work because of the brackets. Is there a reason you have the brackets around the numbers?

Aliaksandr Ilyuk

Thanks Paul! Brackets are for decoration ="[" + (COUNT(CHILDREN())) + "]"

and you were right - SUM(CHILDREN()) works only without them

Paul Newcome

Ok. If you really want to keep the brackets, you will need to insert a helper column that can later be hidden and replicate your counts without the brackets. Then use

=SUM(CHILDREN([Helper Column]@row))