Help Creating a formula using Children
Hi!
I currently am experimenting with Parent and Children Hierarchy to clean up my sheets. Below is a screenshot so you can see my mock up page. The issue I am running into is when I add an additional "Project", my formulas in the columns on the right are pulling from the whole column. I am having difficulty creating the formula to limit the Task Past Completion Date and Completed Tasks to their specific children. The formulas I have now are:
=COUNTIFS([Target Completion Date]:[Target Completion Date], @cell < TODAY(), Status:Status, <>"Complete")
and
=COUNTIF(Status:Status, "Complete")
To get the % of completed tasks I am using the formula =SUM([Completed Tasks]@row / COUNTM(Status:Status))
All in all I would like to specify these formulas just so they tally the data from Project 1, Project 2, etc. and not the whole sheet.
Answers
-
What you'll want to do is move your formulas to the same row as your Project X (the parent) and write your formula using the CHILDREN function so that it only picks up the child rows beneath your parent. Something like:
=COUNTIFS(CHILDREN([Target Completion Date]@row), < TODAY(), CHILDREN(Status@row), <>"Complete")
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.7K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 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!