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
-
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?
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 302 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!