COUNTIFS based on Children criteria of certain row
Context: Please see screenshot attached. Project Manager "Fred" has 2 "active" projects in January: Project 1 and Project 2, which are Children under "Fred". This count of 2 is determined by some percentage of the project being completed.
Goal: Count the number of active projects per month based on % complete being greater than 0.
Request: Can someone please provide me with the appropriate COUNTIF formula based on these criteria? Thank you so much in advance!
Answers
-
A colleague helped me figure it out! Here's the formula he came up with:
(using a hidden helper column called "PM")
=COUNTIFS(CHILDREN($PM$12), "Fred", CHILDREN([Jan 2020]12).
I was having issues at first because various rows were not indented properly and created circular references. But this appears to work fine! If anyone has better suggestions, I'm all ears.
-
Hi Sandy,
Excellent!
Thanks for sharing!
Have a fantastic weekend!
Best,
Andrée Starå
Workflow Consultant / CEO @ WORK BOLD
✅Please help the Community by marking the post that helped answer your question or solve your problem with the accepted answer. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
I believe part of your formula may be missing. You have the range but not the criteria for your second set. Could you please post the full formula?
Additionally... Please don't forget to mark the most appropriate response as being helpful. This will mark your post as having an "Accepted Answer" and will let others having a similar issue know that a solution can be found.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!