Which children have status completed in the last week?

Kate Winter

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!

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.

Kate Winter

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

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