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, join the Smartsheet Guru Elite
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
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 464 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!