Count(children()) of multiple parent rows

I'm trying to count the grandchildren of multiple parent rows. For example, in the header cell I currently have f(x) = "Salesperson: " + Count(Children())

This is only counting the 4 parents rows I have to designate each market area.

What I would like the header to count is how many prospects are in each market area. Is there a Count(grandchildren()) function? I have tried Count(Children(Parent 1)) + Count(Children(Parent 2)) etc. but that number came to over 5 million, when it should have only been 109.

I hope you can help me! Thanks all!

Find more posts tagged with

Comments

John Creason

Megan,

There's no "grandchildren" function, so you'll need to reference each parent in your formula. With your results coming in at over 5 million, it's a sure thing that your formula is doing some double counting.

The CHILDREN function does accept multiple arguments, so you could write it like this:

f(x) = "Salesperson: " + COUNT(CHILDREN(Parent1, Parent2, Parent3, etc...))

The trick is to make sure that you specify the right parent rows. I hope that helps...

John

J. Craig Williams

Megan,

1. Create a new column. I named mine [Hierarchy Level].

2. In the column, add this formula to each cell:

=COUNT(ANCESTORS())

3. Your COUNT of all grand-children will be

=COUNTIF([Hierarchy Level]:[Hierarchy Level], 2)

Note to Smartsheet:

I tried to collapse this into a single formula without adding the extra column like this (where Example is my Primary column)

=COUNTIF(Example:Example, COUNT(ANCESTORS()) = 2)

That returns 0, not the correct value.

This

=COUNTIF(Example:Example, COUNT(ANCESTORS(@cell)) = 2)

returns #UNPARSEABLE

Is there a way to collapse it to the single formula that I am missing? Some nuance of @cell that escapes me so early after my nap?

Craig

ps::

To sum the Grand-Children, the SUMIF of the above (with new column) works too.

Megan Shull

John, your suggestion worked!

John Creason

Craig -

Glad you also posted the Hierarchy Level option. From the second part of your post, it looks like we share a similar preference to avoid using those special columns.

I can't think of a way that @cell is going to get you there... What you tried makes a lot of sense, but exactly how @cell gets processed within the formula engine is not obvious.

Internally, the formula engine and its functions can distinguish between a cell reference and a cell value. What I mean is that it "knows" when you enter "2" or reference a cell with a value of "2". For Example:

=Item1 (where the value at Item1 = 2)

We see the same result on the screen, but internally the formula engine did different work to get there.

Now let's look at your example:

=COUNTIF(Example:Example, COUNT(ANCESTORS(@cell)) = 2)

What's happening in your proposed formula is that @cell gets resolved to the related value at each cell location before it is passed to the ANCESTORS function. In this case, ANCESTORS is seeing the value behind that cell instead of the cell reference - its a subtle but important distinction because ANCESTORS will return #UNPARSEABLE when it's passed a value - it only accepts references.

There are a few special functions such as CHILDREN, PARENT, and ANCESTORS which are expecting a cell reference (or a range). They use the location attribute of reference to go figure out what data you're really after... So when they are just passed a value (as in @cell), they no longer have access to the location information - they can't do their job which is to go get other data, so they return an error. I'll accept that the error message of #UNPARSEABLE is not ideal here, but hopefully you see the issue.

If I've not explained it clearly enough, just say the word, and I'll take another run at it. The short answer, unfortunately, is that you cannot use @cell in CHILDREN, PARENT, or ANCESTORS. I hope that helps - and hopefully we'll get a chance to discuss deeper and in person at the user conference later this year!

All the best,

John

J. Craig Williams

John,

Perfectly clear and I was expecting something like that, though your information really sheds the light on @cell that I was missing before.

That explains why the second attempt does not work.

I think I understand why the first does not work either. I assume it has something to do with the way that ANCESTORS is processing the location when used in the criteria, but it seems to match the logic of the separate column solution.

Is it just an order of operation issue?

I think the #UNPARSEABLE error is OK. It certainly pointed me in the right direction and I've always interpreted as "what are you trying to tell me? the rules we agreed to have been violated".

As for separate columns, I avoid them when the fewer column solution is simple to process from the developer but not when it adds cost on the troubleshooting side.

I'd rather have the maintenance cost be lower.

I can alway hide the columns from the end user.

i can sympathize with some users' demand that there be no extra columns, but I do not agree with them.

Thanks again for the detailed response. That will help as I develop further solutions to interesting problems.

Craig

Susan Alexander

Hi Craig -

I'd like to add a few of wrinkles to your Hierarchy Level solution:

1 - I don't want to count all the grandchildren in the whole sheet, just those of a particular parent

2 - I only want to count the grandchildren if the [Done] column is not checked, i.e. COUNTIF(CHILDREN(Done@row), 0)

3 - I still want it to be dynamic so that as rows are added, no change needs to be made to the parent formula

Can this be done?

Thank you!

Note to Smartsheet:

It seems many people are looking for a 'grandchildren' solution without having to add an additional column. Is this on the roadmap?

Also, I submitted a ticket (03169024) for this, but can't find a way to access it to update it. I'm on an enterprise account.

Thank you!!