Counting all siblings below a master Parent

Ozgur Kalan

Hi,

 

One of my sheets has a master Parent and have several parents and childs below. 

What could be the formula to count all items below any Parent, which counts all parents and childs below?

 

Thanks in advance.

Jill

This would be helpful to me as well.

 

Travis

This can be done with a combination of two functions:

 

COUNT(CHILDREN())

and

SUM(CHILDREND())

 

-Create a new Text/Number column where you will add the formulas and display the count. 

-On each parent row (excluding the master parent) add this formula:

 

=COUNT(CHILDREN(*CellReference*))

 

Replace *CellReference* with the cell reference of the corresponding primary column parent row cell.

 

-On your master parent row AND and parent rows that have parent rows under it, add the following formula:

 

=COUNT(CHILDREN(*CellReference*)) + SUM(CHILDREN())

 

Replace *CellReference* with the cell reference of the corresponding primary column parent row cell.

 

Now, what this will do: each parent row will count the number of children it has. The the master parent will sum these value and count the number of children it has (which are the parents).

 

I made a quick sheet to show you how this works. Formulas are located in the Count column. 

 

https://app.smartsheet.com/b/publish?EQBCT=ff2575da987340aca8a7a87269987217

 

Let me know if you have any questions about how this works! 

Brett Evans

Look at John's formulas and thought processes in this post.  They may be helpful.

 

https://community.smartsheet.com/discussion/check-if-child-row

Ozgur Kalan

Awesome! Working like a charm:

=COUNT(CHILDREN(Task1)) + SUM(CHILDREN()) 

I have pasted all cells the same formula and all items below are counted.

(Except the parent itself, otherwise there pops up another problem, at least I know that counted siblings+1 is the total number of items)

 

Thanks.

alwayzambitious

thanks for this

Tony Barthelemy

Good evening:

 

I am trying to do the same except combine the Grand Parent Row name and Count of all grandchildren in the same row but unsuccessful. My formula below:

Current Formula: =Total  (" + COUNT(CHILDREN()) + ") + SUM(CHILDREN())"