Sum decedents

PDunn

I am seeking help to Sum the total # at the parent level of all children under the secondary parent. Total should appear in the Pandora row which would be the total of Pandora July Offer + Pandora August Offer + Pandora Labor Day Offer. Total should be 12, but my existing formula is counting # of descendants instead of summing each total at the parent rows.

Current formula:

=COUNT(DESCENDANTS(Deliverable@row)) + COUNTIFS(DESCENDANTS(Deliverable@row), ISBLANK(@cell))

image of total assets in creative brief doc 9.6.22.2.jpg

Leibel S

@PDunn

Use the sum formula:

=SUM(CHILDREN())

PDunn

@Leibel Shuchat Thank you Leibel, but this didn't achieve what I am trying to do. I need a column formula that first totals the 4 child rows, which should appear under level 1 parent, then a portion of this formula should sum the level 1 parent rows to achieve a total for all Pandora placements, which should be 12.

I have a level 0 parent with 3 children,

Those 3 children at level 1, have 4 level 2 children.

Paul Newcome

Try this:

=IF(Level@row <> 2, SUM(CHILDREN()), [Number Of Assets]@row)

PDunn

Hi Paul,

@Paul Newcome Thank you Paul. The formula worked to add the level 2 totals, but when I change it into a column formula it doesn't total the level 1 parent levels.

Paul Newcome

Can you provide a screenshot of what you have now? It should be working.

The formula says...

If the Level is not 2 (meaning the level is 1 or zero), then add up the child rows, otherwise pull in number of assets.

PDunn

Hi Paul,

here are a few screen shots

Formula entered in level 0 only

Other rows have this formula which was my previous column formula that was including all descendants

After I change it to use your formula as the column formula this is what appears it doesn't seem to recognize and total up the level 2 children for the level 1 parent.

Paul Newcome

Where exactly are you putting the formula? Which column?

PDunn

In the Grand Total column

Paul Newcome

And are you setting it as a column formula?

PDunn

Yes I converted to a column formula. Here is a new screen shot after I enter the formula you supplied and I change to a column formula. I have indicate changes turned on in the sheet that is why the column is yellow.

Paul Newcome

How exactly is [Number Of Assets] being populated?

PDunn

This is the formula for # of Assets

This is formula to calculate Level

Paul Newcome

Ah. Ok. So the quotes around the numbers output text values (they just look like numbers). Removing the quotes from around the numbers in the [Number of Assets] formula so that it is outputting numerical values should clear things up.

PDunn

@Paul Newcome fantastic, it worked. I had to delete all formulas in that column, save sheet then reenter the formula. I had also used a similar formula to place a numeral 1 in a column, but forgot it was a similar formula on this one and the 1 needed to be reflected as a number not text. Thanks again for your insights. Have a great day

Paul Newcome

Happy to help. 👍️