Is there a formula/function to help count number of children assigned to specific parent tasks?
Hi all,
I'm trying to put together a table that lists the number of sub-tasks (children) in each respective Workstream (parent) of a specific Project Plan, and also breaks these down into their respective Health Status (Green, Yellow, Red).
I am aware that I can apply a "COUNTIF" formula and set the range to encompass the children underneath each respective parent group. However, my concern is that the formulae will need to be subsequently updated each time more tasks are added to a given workstream.
On that note, is there a formula or functionality I can use that detects a parent/child relationship, and that I can use in this instance?
Many thanks in advance for your help on this, I look forward to hearing from you!
Kind regards,
Gerhard
Best Answer
-
What you will need to do is add a column to the source sheet (that can be hidden after setup) and use this column formula:
=PARENT([Task Name]@row)
Then you would use this column as the range in a COUNTIFS and the criteria would be that it equals [Column 2]@row.
Answers
-
Using the CHILDREN function for your range should account for additions to the child rows.
=COUNTIFS(CHILDREN([Column Name]@row), "Red")
-
Hi Paul,
Thank you for getting back to me, and for your suggestion. I wasn't familiar with the formula and it seems most useful!
Unfortunately, I can't seem to reference sheets outside of the document I am using as the database for the dashboard, which is meant to collate data from the sheet (Project Plan) I referenced above.
Is there a workaround I can implement, or another formula you may know of?
Many thanks once again, and I look forward to hearing from you!
Kind regards,
Gerhard
-
Are you able to provide screenshots with sample data that show the source sheet and target sheet?
-
Hi Paul,
I had to recreate these as the information I'm working with is confidential, but hopefully these will be illustrative enough.
As you can see from images [1] and [2], the COUNT( formula allows us to reference another sheet, and select the range of cells required. My trouble with this is if new tasks (and by extension cells) are added below this range, the formula will not pick these up and will have to be updated.
The CHILDREN( formula, in turn, appears to only allow cells within the same sheet to be referenced - see image [3]. Is there a way to work around this?
Many thanks once again for your time and help on this matter!
Kind regards,
Gerhard
-
What you will need to do is add a column to the source sheet (that can be hidden after setup) and use this column formula:
=PARENT([Task Name]@row)
Then you would use this column as the range in a COUNTIFS and the criteria would be that it equals [Column 2]@row.
-
@Paul@Paul Newcome, just realised I never thank you for your help on this! Apologies for the delay, and thank you very much!
All the best,
Gerhard
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.9K Get Help
- 410 Global Discussions
- 220 Industry Talk
- 457 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 298 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!