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.

how to count rows under a parent and sub parent(s)

Jon Passman
edited 12/09/19 in Archived 2016 Posts

I have a sheet that is grouped by product categories. Underneath that category are products, and within/under a product are projects that are active and/or planned.  So imagine the structure of the sheet is:


Product category

   Product A

      Project A

      Project B

      Project C

   Product B

      Project A

      Project B


I would like to display a count of both products and projects next to the product category, like this:  Product category - 2 - 5


Can i do this? if yes how?




  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭

    Use the COUNT, SUM, and CHILDREN functions


    I would create one column to get the count of children.



    Another column to determine if the row is a parent and if so, get both the current rows value and the sum of the children


    Something like this


    =IF(Counter23 > 0, "Product Category - " + Counter23 + " - " + SUM(CHILDREN(Counter23)), "")


    where Counter is my column name and row 23 is where the formula is.


    If you have more than 2 tiers, it is more difficult, but still possible.




  • Jon Passman
    edited 05/12/16

    Thank you.  I will try this.

This discussion has been closed.