Sumifs and Children

Dave Lannom

I am trying to sum the values of child row if three conditions in adjacent columns are met, from another sheet and post it on my main sheet.  I reference a parent row and need to sum the children values if they meet the three conditions .  

Specifically, I use a cell value from the main sheet to find the referenced parent row in the other sheet and then sum the POINTS column if the values by rows, in next columns meet the following conditions: Type = "story", Status = "closed", and Resolution is either "fixed" or "done".

I have tried the SUMIFS with the CHILDREN() and a VLOOKUP but am getting nowhere.

Help.

Paul Newcome

I would suggest summing the points on the other sheet and then using cell linking or x-sheet references to pull the totals to your main sheet. To sum on the other sheet, try this in the points column of each parent row...

 

=SUMIFS(CHILDREN(Points@row), CHILDREN(Type@row), @cell = "story", CHILDREN(Status@row), @cell = "closed", CHILDREN(Resolution@row), OR(@cell = "fixed", @cell = "done"))

 

Otherwise you will be creating A LOT of ranges for x-sheet references.

Dave Lannom

thx