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

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

 

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

 

· Share on FacebookShare on Twitter

Comments

  • Charles DESCOTES
    Charles DESCOTES ✭
    Options

    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 ?

    · Share on FacebookShare on Twitter
  • Randall Smith
    Randall Smith ✭
    Options

    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

    · Share on FacebookShare on Twitter
This discussion has been closed.

Categories