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.
Formula to count/sum grandchildren
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
Comments

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).

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.

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.

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.

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!

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!

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

 Well I did 3 things:
 Turned on dependencies
 Created a fresh sheet
 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 ;) )

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.

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