Is there a formula/function to help count number of children assigned to specific parent tasks?

Gerhard da Costa Pinto

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

Paul Newcome

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 Newcome

Using the CHILDREN function for your range should account for additions to the child rows.

=COUNTIFS(CHILDREN([Column Name]@row), "Red")

Gerhard da Costa Pinto

Using the CHILDREN function for your range should account for additions to the child rows.<\/p>

=COUNTIFS(CHILDREN([Column Name]@row), "Red")<\/p>","bodyRaw":"[{\"insert\":\"Using the CHILDREN function for your range should account for additions to the child rows.\\n\\n=COUNTIFS(CHILDREN([Column Name]@row), \\\"Red\\\")\\n\"}]","format":"rich","dateInserted":"2021-10-25T13:26:18+00:00","insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-08-25T17:46:09+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/308810#Comment_308810","embedType":"quote"}"> https://community.smartsheet.com/discussion/comment/308810#Comment_308810

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.

有解决我可以实现,或另一个吗formula you may know of?

Many thanks once again, and I look forward to hearing from you!

Kind regards,

Gerhard

Paul Newcome

Are you able to provide screenshots with sample data that show the source sheet and target sheet?

Gerhard da Costa Pinto

Are you able to provide screenshots with sample data that show the source sheet and target sheet?<\/p>","bodyRaw":"[{\"insert\":\"Are you able to provide screenshots with sample data that show the source sheet and target sheet?\\n\"}]","format":"rich","dateInserted":"2021-10-25T16:21:20+00:00","insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-08-25T17:46:09+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/308853#Comment_308853","embedType":"quote"}"> https://community.smartsheet.com/discussion/comment/308853#Comment_308853

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], theCOUNT(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.

TheCHILDREN(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

Paul Newcome

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.

Gerhard da Costa Pinto

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:<\/p>

=PARENT([Task Name]@row)<\/p>

Then you would use this column as the range in a COUNTIFS and the criteria would be that it equals [Column 2]@row.<\/p>","bodyRaw":"[{\"insert\":\"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:\\n=PARENT([Task Name]@row)\\n\\nThen you would use this column as the range in a COUNTIFS and the criteria would be that it equals [Column 2]\"},{\"attributes\":{\"mention-autocomplete\":true},\"insert\":\"@row.\"},{\"insert\":\"\\n\"}]","format":"rich","dateInserted":"2021-10-25T19:42:41+00:00","insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-08-25T17:46:09+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/308874#Comment_308874","embedType":"quote"}"> https://community.smartsheet.com/discussion/comment/308874#Comment_308874

@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

Paul Newcome

Happy to help.️