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.

=SUMIF across all children / grandchildren

Randall Smith
edited 12/09/19 in Archived 2016 Posts

Im not a power user but im looking to build out a bidding teplet for my shop.  I have a lot of info spread across many children / grandchildren catagories.  Heres what Im looking to accomplish.



- Animals

- - Cat

- - - Brown 2

- - - Red 4

- - - White 1

- - Dog

- - - Brown 4

- - - Red 2

- - - White 6

- - Fish

- - - Brown 1

- - - Red 8

- - - White 3

I would like to know the sum of Red fish without having to sum all the number back up into the parent then running a sum from there.  What I would love to work would be something like this:
=SUMIF(children(Items), "Red", children(number))
Obviously this is a small data set.  An actual use for this would also need to include a kind of =SUMIFS functionality:
=SUMIFS(children(Items), "Red", children(Items), "Brown", children(number))
Sorry if this is confusing.  Just looking streamline and figured this would help.



  • You could rather display yur data in a table fashion rather partents and children.



    1 column "Category" → this one will only bear "animal" in your example

    2. column "Species" → this one will either bear cat, dog or fish

    3. column "Color" → this one will either bear red, brown or white

    4. column "Quantity" → address the numbers.

    So you end up having a table of 4 columns over 9 rows from your example.


    On top of it insert a new row  of blank cells,

    I recommend you customize the colors for this row, because it is going to be pecial:

    in each of the first 3 blank cells, in columns Categoy, Species and Color, you can type whatever is present below in your table = it is going to be your request.

    The fourth cell - on top of Quantities - will be calculated automatically by the following formula:

    =SUMIFS(Category2:Color10, Category2:Category10, Category1, Species2:Species10,Species1, Color2:Color10,Color1)


    Does it answer your question ?

  • This does answere the question, and this was how I had set things up in my Google Doc.  The problem with the doc was that I had way too many rows and It was hard to visualy track all the parts quickly.  I wanted to switch to smart sheet so that I could collaps unused rows and and still display my results.  


    That said, I do think I was able to find a work around using =SUMIF(CHILDREN()).  It was not what I was looking for exactaly but it doing the job.


    Thanks again for your help.  Im loving the flexablitiy of this applicaiton!



This discussion has been closed.