Trying to count children (level 1) from a reference sheet

Sean Corcoran

I hope some can help me. I need to count how many level 1 task I have on a reference sheet.

I have no problem using the following formula in the sheet summary that the task are in and get the correct number of level 1children.

=COUNT(CHILDREN(Details1))

I have a summary sheet for my dashboards that need to look at another sheet as a reference. When I use the following formula that looks correct to me I get #UNPARSEABBL in the cell. The reference {Detail} that points to the other sheet works for other formulas in my summary sheet but not with the one below. I am thinking it has to do with where I have the 1?

=COUNT(CHILDREN({Details}1))

Any help would be great

Kelly Moore

Hey Sean

Currently the Children and Parent functions don't support cross sheet references. The work around is to add a helper column to the source sheet that either (1) designates the level (using ANCESTORS function), or (2) indicates if it is a parent by counting children. This is typically my preferred way. This added functionality allows me to conditionally format my parent rows and I can use it to filter some of my automated workflows. I call this column Parent and it is a checkbox column

=IF(COUNT(CHILDREN(Primary column))>0,1)

If you wanted to use hierarchy Level

=COUNT(ANCESTORS(Primary column))

Be sure to change the Primary column designation to the name of your actual primary column.

Once you add the helper column, my original formula will work for you if you chose the Ancestors option, replacing {Details} with the reference to the new Helper column. If you used the checkbox option, a child row would remain unchecked so the countifs would be =COUNTIFS({Helper parent checkbox column},0). Again, replace reference with the actual column name.

Kelly Moore

Hello Sean

Try this

=COUNTIFS({Details},1)

I am assuming that the Details column in your other sheet has the Level numbers in it. This formula will count the cells that have 1 in it.

Sean Corcoran

@KDM thanks for your reply but that wont work because I don't have a "1" in the cell. What I am trying to do is count all items that are sitting at level 1 (indented once). Like I said using the count children function worked within the sheet but not when I was trying to reference it from another sheet.

Below is what I am trying to count. Everything that is a child of "Open SOP's"

Kelly Moore

Hey Sean

Currently the Children and Parent functions don't support cross sheet references. The work around is to add a helper column to the source sheet that either (1) designates the level (using ANCESTORS function), or (2) indicates if it is a parent by counting children. This is typically my preferred way. This added functionality allows me to conditionally format my parent rows and I can use it to filter some of my automated workflows. I call this column Parent and it is a checkbox column

=IF(COUNT(CHILDREN(Primary column))>0,1)

If you wanted to use hierarchy Level

=COUNT(ANCESTORS(Primary column))

Be sure to change the Primary column designation to the name of your actual primary column.

Once you add the helper column, my original formula will work for you if you chose the Ancestors option, replacing {Details} with the reference to the new Helper column. If you used the checkbox option, a child row would remain unchecked so the countifs would be =COUNTIFS({Helper parent checkbox column},0). Again, replace reference with the actual column name.

Sean Corcoran

@KDM thanks for the workaround, I am finding out more and more that Smartsheet is not all that Smart.