How to count the number of children based on certain value of parent

Chuen

The column name is [Task] and a cell value called "Project Details" is located somewhere in the column. Their children is project 1, project2, ... etc....

I want to count the number of children under "Project Details". I know that if the position of the cell "Project Details" is fixed. I can use formula like this: count(children([Task]1))

However, the parent cell is not always in the same position. I try the following formula but it is not working:

countif([Task]:[Task], Parent(@cell) = "Project Details")

Andrée Starå

Hi Chuen,

Try something like this. (Task9 is the parent)

=COUNT(CHILDREN(Task9)) 

Did it work?

I hope that helps!

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post help answer your question or solve your problem? Please help the Community by marking it as the accepted answer. It will make it easier for others to find a solution or help to answer!

Chuen

Hi Starå, but sadly I would like to input it as a sheet summary.

And the parent cell is not always in the same row in the column.

Therefore, your answer does not work for me.

But, thanks anyway.

Paul Newcome

Unfortunately @cell references not work with hierarchy functions. Please feel free to Submit a Product Enhancement Request.

In the meantime, you can add in an extra column (that can be hidden after setup). In this column you would use the formula of

=PARENT(Task@row)

This will pull the parent value. Then you can use the COUNTIFS on this new helper column such as

=COUNTIFS([Helper Column]:[Helper Column], "Project Details")

Andrée Starå

@Chuen

Sorry about that!

I misread your question!

I saw that Paul answered already!

Let me know if I can help with anything else!

Best, 

Andrée