Which children have status completed in the last week?

I have a smartsheet tracking development of a new product and I want to put in the summary sheet how many units have been completed in the past week. How do I do that using the children function? Thanks!


Answers

  • David Joyeuse
    David Joyeuse ✭✭✭✭✭

    Hi @Kate Winter

    You're gonna need to go with COUNTIFS here.

    =COUNTIFS([Task Name]:[Task Name], COUNT(ANCESTORS(@cell))>0, [Status]:[Status], "Completed", [Status date]:[Status date], WEEKNUMBER(@cell)= (WEEKNUMBER(TODAY()) -1))


    Should do it.

  • Hm, it came back as unparseable as written here. When I change it to:

    =COUNTIFS([Task Name]:[Task Name], COUNT(CHILDREN([Task Name]32)) > 0, Status:Status, "Completed", [Status date]:[Status date], WEEKNUMBER(@cell) = (WEEKNUMBER(TODAY()) - 1))

    it seems to be able to calculate but it's incorrect. It comes back as zero when I would expect 2. Maybe I need to note that this screenshot is only part of my sheet--there are many other tasks and I'm trying to narrow in on the children of "pilot unit assembly" (which is row 32). I'm still learning the @cell function--do I need to adjust something there?

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭

    Hi @Kate Winter ,

    Did you get this to work?

    I suspect part, if not all, of your issue is timing. WEEKNUMBER formulas have issues at the beginning and end of a year. Your dates were at the end of a year with 53 weeks and the week with Jan 1 is always week 1. Your formula is WEEKNUMBER -1 which works great accept when this week is 1 and last week was 52 or 53. 1 - 1 is 0.

    Your formal looks good. Does it work now?

    =COUNTIFS([Task Name]:[Task Name], COUNT(CHILDREN([Task Name]32)) > 0, Status:Status, "Completed", [Status date]:[Status date], WEEKNUMBER(@cell) = (WEEKNUMBER(TODAY()) - 1))

    If it works I can help you create the fix so it works at the beginning and end of the year.

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!