Sum decedents
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))
Answers
-
-
@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.
-
Try this:
=IF(Level@row <> 2, SUM(CHILDREN()), [Number Of Assets]@row)
-
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.
-
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.
-
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.
-
Where exactly are you putting the formula? Which column?
-
In the Grand Total column
-
And are you setting it as a column formula?
-
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.
-
How exactly is [Number Of Assets] being populated?
-
This is the formula for # of Assets
This is formula to calculate Level
-
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.
-
@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
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!