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

D Morgan
D Morgan
edited 12/09/19 in Archived 2015 Posts

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

Tags:

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.  

  • Travis
    Travis Employee

    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

    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
    Travis Employee
    edited 06/09/15

    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

This discussion has been closed.