Question about SUMIFS with CHILDREN with multiple criteria

Mike.Brown
Mike.Brown ✭✭
edited 09/19/22 in Formulas and Functions

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

  • Toufong Vang
    Toufong Vang ✭✭✭✭✭
    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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!