Welcome to the Smartsheet Forum Archives


The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Counting all siblings below a master Parent

Ozgur Kalan
edited 12/09/19 in Archived 2015 Posts

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.

Tags:

Comments

  • Jill
    Jill
    edited 10/31/15

    This would be helpful to me as well.

     

  • Travis
    Travis Employee

    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
    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
    edited 11/02/15

    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.

  • Tony Barthelemy
    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())"

     

This discussion has been closed.