Question about SUMIFS with CHILDREN with multiple criteria

Mike.Brown

The sheet summary formula "TEST" which is looking for the number of recorded docs Alex did for Friday. What I have right now is =SUMIFS(CHILDREN(Recorded:Recorded), CHILDREN(Name:Name), "Alex", CHILDREN(Day:Day), TODAY(-3)). It works if I remove either the day criteria or the name criteria but returns zero as it is above. What I am trying to get per the sheet below is 11.

TVang

Hi, @Mike.Brown,

Another approach is to use a helper column, e.g., Ancestors. In that column, use the functions JOIN() and ANCESTORS() to generate a string containing the ancestors of the cells in the Name column. =JOIN( ANCESTORS( Name@cell ) )

Modify your SUMIFS() formula to return a sum from the Recorded column where the string in Ancestors contains "Alex" and the date value in Day is 3 days ago.

=SUMIFS( Recorded:Recorded, Ancestors:Ancestors, CONTAINS( "Alex", @cell), Day:Day, TODAY(-3) )

Cheers!

Darren Mullen

@Mike.Brown In your example the name "Alex" is only on one row, so when you are trying to use the Name and Date as the criteria, there are no matches. You'd have to extend the name onto the rows that have the dates, or roll up the data you want to the parent row with the name "Alex" on it in your example.

Mike.Brown

That makes sense. Do you know how I would get the info that I am looking for? Short of entering their name in every row

Thanks

TVang

Hi, @Mike.Brown,

Another approach is to use a helper column, e.g., Ancestors. In that column, use the functions JOIN() and ANCESTORS() to generate a string containing the ancestors of the cells in the Name column. =JOIN( ANCESTORS( Name@cell ) )

Modify your SUMIFS() formula to return a sum from the Recorded column where the string in Ancestors contains "Alex" and the date value in Day is 3 days ago.

=SUMIFS( Recorded:Recorded, Ancestors:Ancestors, CONTAINS( "Alex", @cell), Day:Day, TODAY(-3) )

Cheers!

Mike.Brown

That worked great! Thank you!