Check if Child Row

John Sauber

We use the formula =IF(COUNT(CHILDREN()) > 0, 1, 0) to check a box if a row is a parent row. This is a helpful tool when using conditional formatting to format rows which are parents, automatically.

 

We'd like to do a similar thing if a row is a child row, but I cannot think of a way to do perform this logic. Has anyone done this before, or does this question spark any ideas from anyone?

 

Thanks!

 

John

John Sauber

It turns out, what we were actually looking for was if there were any grandchildren in the sheet, and if so, flag the grandparent row.  We were able to get this done using the "Is Parent?" column indicated in the initial thread. The formula (we call it "Is Grandparent?") is below:

 

=IF(AND(COUNT(CHILDREN()) <> IF([Is Parent?]1 = 1, COUNTIF(CHILDREN([Is Parent?]1), 0), 0), [Is Parent?]1 = 1), 1, 0)

SashaR

I am adding that one (is grandparent) to my formula sheet! Here is one I build as I was trying to help you with your first post. Not exactly what you are looking for however it could help someone!

 

This will identify (check the box) of all non-parent rows with data.

 

=IF(ISBLANK([Task Name]22), 0, IF(COUNT(CHILDREN([Task Name]22)) > 0, 0, 1))

 

Task Name is the primary column. 

John Sauber

Hi SashaR, thanks for your comment.

 

For our needs in the origial post, we want to know if the row is a child. This is different than testing if the row is not a parent, which is what your formula does. A row can be a parent and a child, and we'd want to check that row. The formula you shared does not work in this case, just so you know for your uses. In other words, if you have more than a 2-level hierarchy, you're going to need a different formula, which is what I was originally seeking.

John Sauber

That'd be cool, Brett! I'm glad you're liking it!

Brett Evans

I've done the aout-numbering based on putting each each level of a heirarchy in different columns, but that doesn't map to the way people naturally make heirarchys in this product.

Brett Evans

This is great stuff.  I am going to play around with this and see if I can use it in an outo-numbering system.  I would love to use it to create a system where as you make children, grand kids and so on, the numbering keeps up and you end up with numbers like this:

|1 |  Parent |

   |1.1| Child |

       | 1.1.1|  grandkid|

 

When I nail it I'll let you know.

Jared Bieschke

Hi Brett - this sounds like something I am really interested in finding a way to do - we use this for a WBS for projects, so be able to identify the task, subtask, and subsubtask numbers are really valuable - we're hand-keying in now, but would like to be able to make them flexible and automatic as we move work breakdown items up or down in the chart. 

 

Will keep checking here for updates and post anything I can work out!

rlalonde20486

Hi, 

Some people may benefit from this formula.

The issue is that in scrum teams, there are a number of people on a team, but the work is assigned to a team, not an individual.

So i added some columns and created this formula

=IF(COUNT(CHILDREN()) <> 0, SUM(CHILDREN()), IF(ISNUMBER(People3), People3, 0) * IF(ISNUMBER(Allocation3), Allocation3, 100) * Duration3)

 

It sums up the work against a summary task, and then does the for the child task.

Michael.Baker

isParent  =IF(COUNT(CHILDREN()) > 0, 1, 0)

isChild  =IF(COUNT(ANCESTORS()) > 0, 1, 0)

Generation =COUNT(ANCESTORS())

 

 

ib

So i'm trying to calculate only the sum of all leaf nodes so that I don't double count, but the following:

=SUMIFS(points:points, Task:Task, (COUNT(CHILDREN(Task:Task) > 0)))

Counts the number of children in tasks column, not just Task1 for instance.  Is there a way to do this? Basically I want a Sum of all the tasks that are not parents, while preserving rollup sums within the the sheet for stories/epics.