How to Count Descendants but remove children that have children?

KimTDSYN

I have this scenario:

The "API" count should be 11. I need for it to exclude the "reseller level" child since it's just a grouping for the 10 items in it.

I have several groupings like this so I want a formula to apply to all cells. I don't want to manually tell it to exclude a number. I can't "-children()" because then I'll lose the one child I want to count.

Paul Newcome

Ok. Try updating the helper column formula to:

=IF(COUNT(CHILDREN([Primary Column Name]@row )) = 0, 1)

Then this:

="API (" + SUM(DESCENDANTS([Helper]@row)) + ")"

Paul Newcome

You would need a helper column such as a checkbox type that can be hidden after setting up with a formula to flag parent rows:

=IF(COUNT(CHILDREN([Primary Column Name]@row)) > 0, 1)

Then incorporate this into your existing formula.

KimTDSYN

1. I don't think this will work. It just gives me a "1" for every child that has kids but not a count of number of children that have kids, for that parent. So if I have 2 children with kids I need to subtract 2, not 1, from the value. I can't sum all the "1" in helper column for the child because that list will grow as I add entries.

2. Regardless of the above I tried to use the value in my calc but getting the wrong value.

Current calc ="API (" + COUNT(DESCENDANTS()) + ")"

new ="API (" + COUNT(DESCENDANTS() - Helper@row ) + ")" but instead of getting 11 (12-1), I'm getting 1.

I'm missing something.

Paul Newcome

In addition to the helper column, your existing formula would change to this:

="API (" + COUNTIFS(DESCENDANTS([Helper Column]@row), @cell <> 1) + ")"

KimTDSYN

The logic makes sense but I'm getting zero.

="API (" + COUNTIFS(DESCENDANTS([Helper]@row ), @cell <> 1) + ")"

Paul Newcome

Ok. Try updating the helper column formula to:

=IF(COUNT(CHILDREN([Primary Column Name]@row )) = 0, 1)

Then this:

="API (" + SUM(DESCENDANTS([Helper]@row)) + ")"

KimTDSYN

That worked! Thank you.

Paul Newcome

Happ to help. 👍️