Formula to count/sum grandchildren

D Morgan

Is there a formula that can be used to count or sum the grandchildren of a parent row?

For Instance: 

• Parent Row: United States
•    Child Row: East
•       Grandchild: Florida
•       Grandchild: Georgia
•    Child Row: West
•       Grandchild: California
•       Grandchild: Oregon
•    Child Row: Central
•       Grandchild: Illinois
•       Grandchild: Texas
•       Grandchild: Oklahoma

Count the states for the US Region

Ajay Jindal

For your example, use the formula =COUNT(CHILDREN()) on the child rows that will count the grand children and use =SUM(CHILDREN()) on the parent row to add up the totals for all the child rows.

 

One thing to note is that COUNT(CHILDREN()) will only count the child row if there is content in the cell in that column (it ignores blank cells).  You can use COUNT(CHILDREN([primary column]x) - so it will effectively be looking at a different columns (the primary column is a good choice).

D Morgan

Thanks Ajay.

This is how I'm currently doing it, but was hoping to find a single formula to sum the grand children. versus updating multiple formulas each time a new region or subheading is added.

Ajay Jindal

You only have to add one new formula when a new summary row is added and everything else will work out without modification.  Tip: you can simply copy and paste it from another summary row.

 

 

 

Nohelani Ka

My team is currently using a variation of the team task list template that references the children count formula. One particular category isn't displaying the count, just '0'. I've tried copying and pasting the formula form another category where the formula is working. I've typed out the formula and still no luck. Can you advise? Thank you in advance.  

Travis

Nohelani - the count children formula will only count child rows that contain data. If your formula is in a column without any text in the child rows, they will not be counted (even if there is data elsewhere in those rows). You can get around this by referencing the primary column in your count formula (which will likely contain data in all the child rows). The formula would look like this:

 

=COUNT(CHILDREN([Primary Column]1))

 

Let us know if this isn’t the issue! 

Nohelani Ka

Travis, thanks for your suggestion! After taking a closer look the formula was ok, somehow the primary column for that section lost it's 'parent' classification for the rows in question. Child rows have been highlighted and we're good to go. Thanks!

Jeffrey Arlt

I'm using Smarsheet in Chrome on a MacBook Air 10.10.3.  I can not get the Sum/Count formulat to work properly.  Thanks for your assistance.

 

Here is the simple task list with fx in ()

 

Performance - 0 (="Performance - " + SUM(CHILDREN()))

  Verify Coverage - 4 (=" Verify Coverage - " + COUNT(CHILDREN()))

    Main Lab

    Shore Lab

    Norte

    Marine Ops

  Verify Bandwidth - 4 (=" Verify Coverage - " + COUNT(CHILDREN()))

    Main Lab

    Shore Lab

    Norte

    Marine Ops

 

If I switch the SUM and COUNT:

Performance - 2 (="Performance - " + COUNT(CHILDREN()))

  Verify Coverage - 0 (=" Verify Coverage - " + SUM(CHILDREN()))

    Main Lab

    Shore Lab

    Norte

    Marine Ops

  Verify Coverage - 0 (=" Verify Coverage - " + SUM(CHILDREN()))

    Main Lab

    Shore Lab

    Norte

    Marine Ops

Jeffrey Arlt

1. Well I did 3 things:
2. Turned on dependencies
3. Created a fresh sheet
4. modified fx formulas

 

Here is the new sheet that is working:

 

Performance - 8 ( ="Performance - " + COUNT(CHILDREN([Task Description]2:[Task Description]7)) )

  Verify Coverage - 4 ( =" Verify Coverage - " + COUNT(CHILDREN()) )

    Main Lab

    Shore Lab

    Norte

    Marine Ops

  Verify Bandwidth - 4 ( ="Verify Bandwidth - " + COUNT(CHILDREN()) )

    Main Lab

    Shore Lab

    Norte

    Marine Ops

Security - 3 ( ="Security - " + COUNT(CHILDREN()) )

  Authentication

  Guest

  Devices

 

This is working now but will probably break when I add rows.  I could not get the fx 'SUM' to work and would appreciate being point ed to an answer to this.  Also, could the SUM issue be a bug in Chrome and Smartsheets? (or just a BUG in me ;-) )

Travis

Hi Jeffrey, when you concatenate a formula with text, the entire result will be formatted as text (even if it contains numbers). This is why the SUM isn’t working - you cannot SUM text. You could add an additional Text/Number column where you can add your COUNT(CHILDREN() formulas, then you can SUM this in your “Performance” parent row. Once you have these formulas set up, you could hide the new column. 

Bruno Simoes

Since Aug 06, they've released the "Ancestors()" to check the number of ancestors. This solved my problems to check for multiple levels of children like this (I use 5 levels of tasks).

 

This function checks to see if the task if a child (hence no formatting is applied), else it checks the number of ancestors (with "zero" being the parent of all parents)

=IF(COUNT(CHILDREN(Task1)) = 0, "", IF(COUNT(ANCESTORS()) < 1, "Cheio", IF(COUNT(ANCESTORS()) < 2, "Três quartos", IF(COUNT(ANCESTORS())<3, "Meio", "Um quarto"))))

 

Works like a charm