=SUMIF across all children / grandchildren

Randall Smith

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

 

Charles DESCOTES

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 ?