Question about SUMIFS with CHILDREN with multiple criteria

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.
Best Answer
-
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!
Answers
-
@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.
Darren Mullen, smartsheetguru.com
Get my 7 Smartsheet tips here
Author of: Smartsheet Architecture Solutions
-
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
-
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!
-
That worked great! Thank you!
Help Article Resources
Categories
Check out the Formula Handbook template!