Count children & grandchildren of multiple rows under main parent

I'm trying to count the grandchildren status of multiple parent rows, and show a summary under mainparent (binos/mgmt/dmi).

I'm using fx="Complete (" + COUNTIF(CHILDREN(), "Complete") + "),In Progress (" + COUNTIF(CHILDREN(), "In Progress") + ") , Not Started(" + COUNTIF(CHILDREN(), "Not Started") + ")" to get the summary of the status for each parent (row19 and 22), and trying to sum them to display in row18.

Hope someone could help.

Thanks

Find more posts tagged with

Formulas

Comments

mthasen

screen capture

J. Craig Williams

There might be a way to have this in a single cell, but I doubt it.

There might be a way to have this in a single column, but I haven't seen it yet.

The primary issue is that you are trying to sum three ranges, which is possible but not intuitive after happy hour has started.

The second issue is that you have complicated things by converting the 3 numbers to a long text string to parse.

I guess the last issue which is either number 3 or number 0, is that Smartsheet has neither a GRANDCHILDREN() function, nor a DESCENDANTS(), both of which might be useful here.

That said, I would create three columns to capture the three counts and use them in both the parent text and the grandparent text.

I have one more idea to try to collapse to a single column, but I'm not optimistic it will work. I'll post it if it does.

I hope that helps.

Craig

Paul Newcome

I suggest what Craig said. Have separate columns for each status. Using the CHILDREN() function in all 3 rows will accomplish what you are looking for as rows 19 and 22 will capture the "grandchildren" rows and row 18 will capture 19 and 22.

mthasen

Thanks Craig & Paul.

I was able to get the overall summry with this formula.

="Complete (" + COUNTIF(CHILDREN(Status2:Status2707), "Complete") + "),In Progress (" + COUNTIF(CHILDREN(Status2:Status2707), "In Progress") + "),Not Started (" + COUNTIF(CHILDREN(Status2:Status2707), "Not Started") + ")"

J. Craig Williams

That is pretty cool and I am really surprised it works.

This might actually be a bug. The doc clearly states the argument for CHILDREN() is A CELL, not a range. Other instance of ranges have failed to return a valid value.

Be aware that if 2707 is not the last row in the hierarchy of row 1, you may have odd results.

If there was a way to avoid that limitation, there might be other use cases for this.

Craig

Paul Newcome

The doc clearly states the argument for CHILDREN() is A CELL, not a range.

Could you explain what you mean by this? I have always used CHILDREN() to identify a range as in "all of the children of the row that my formula is in". If I am referencing a different column from the column my formula is in, I use CHILDREN([Column Name]:[Column Name])

Be aware that if 2707 is not the last row in the hierarchy of row 1, you may have odd results.

This is why I use CHILDREN() or CHILDREN(Column Reference) instead of specifying rows. The CHILDREN function is pretty good at identifying hierarchies within hierarchies. Putting it in a Parent row will only count the immediate children (not grandchildren, etc.). Putting it in a Child row will only count the immediate children of that (the grandchildren), but not any lower levels (great grandchildren).

If you put it in a child row, you'll get a summary of the grandchild rows. If you then in turn put it in a parent row, you will get a summary of the child rows. So on and so forth. I do a lot of different types of project plans and use a lot of automation for them.

I love using the CHILDREN() function to provide summaries of sub-tasks and overall completion, etc. I even use MIN(CHILDREN()) and MAX(CHILDREN()) to provide summaries of start and finish dates.

All of which are looking at ranges based off of the row that my formula is actually in.

And after all that... I probably just misunderstood what you meant. Haha

J. Craig Williams

CHILDREN() returns a range.

CHILDREN(argument) expects (according to documentation) a cell.

Craig

Paul Newcome

Hmm... You're right (of course Haha). I never really thought about it, but now that you point it out I see what you mean.

I wonder if maybe the wording in their documentation is just off as I have seen in some of their videos where they do actually specify a range as opposed to a cell.

J. Craig Williams

re: You're right (of course Haha)

Ah, if only that were true. I've added CHILDREN() investigation to my list, but likely won't get to it until at least next week.

Craig

Sign in to join the conversation!

Quick Links

Trending discussions

Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?
Need Help with If, Index, Collect
Please help, I have a few of these to do. I have a nested IF formula. The first part is an INDEX/MATCH and works as a standalone. The next few are INDEX/COLLECT. These are not working as standalone or in the nested version (Unparsable Error). Any ideas? =IF(CONTAINS(“A/V”, [Vendor Type]@row ), INDEX(COLLECT({Date Secured…