Archived 2016 Posts

Archived 2016 Posts

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

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

 

Itmes

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

 

Comments

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

     

    Proposal:

    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!

     

    R

This discussion has been closed.

Trending Posts