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

Jon Passman

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?

 

Thanks

J. Craig Williams

Use the COUNT, SUM, and CHILDREN functions

 

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

=COUNT(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.

 

Craig