How do you calculate the cumulative total of children rows only

SO123

How do you calculate the cumulative total of children rows only in a column? I have a column of contract contingency amount, another column showing draws from this contingency and I want to create a third column with a cumulative total of how much is left of this total contingency amount after each withdrawal. But I only want to calculate this cumulative remaining total using the child rows as I have multiple parent/child relationships with each parent representing a different total contingency amount as the start point for the withdrawals.

Antonie B

@SO123 -

Smartsheet has several functions that allows you to tap into hierarchy relations on your sheet.

The full function list can be found here: https://help.smartsheet.com/functions

The function you will want to use is called CHILDREN: https://help.smartsheet.com/function/children

This article goes into more debt on using hierarchy formula functions: https://help.smartsheet.com/articles/2476811-reference-children-parents-ancestors-hierarchy-functions

In your case, I would recommend setting the formula at the parent level, using =SUM(CHILDREN()) to roll up both the contract contingency amount & draws and then use subtraction to show the cumulative remaining total.

I hope this helps.

SO123

Thanks for the response Antoine. My question is more around how create a cumulative total that just applies to child rows. I've seen other posts around calculating cumulative totals suggest creating a helper automated number column to use to sum the numbers above or below. However, I only want to have a rolling sum of the child rows in another column so this doesn't work. I would need the calculation at the child row level and not just the parent level unfortunately. In other words, in one column I would have a total of 100,000 total contingency. In other column, there is a draw of 10,000 and in another row (at another time), another draw of 10,000 is made. In the third column I want to show that in the first case, 90,000 was left and in the next row that 80,000 is left (i.e. a running total of what's left for each row and not just the parent roll up). This there a formula that would left me do this?

KPH

Could you place an IF function around your cumulative calculation to only do the calculation on rows where the SUM(CHILDREN())=0 (i.e. the children rows)?

I am thinking

=IF(SUM(CHILDREN())=0, formula for cumulative total)

Within the () after CHILDREN enter the column name for a column that is always populated.

KPH

By SUM I actually mean COUNT, sorry, long day. To only include the child rows you can check the COUNT if children is 0. This will exclude any rows with Children.

=IF(COUNT(CHILDREN())=0, formula for cumulative total)

SO123

Thanks @KPH I'm still getting errors when I try to write the formula. Here is an image of what I'm trying to achieve. I want to formula to calculate the numbers circled in yellow for each row. Each of the yellow lines indicates a new parent/child hierarchy. I want the formula to apply to only the children in that particular hierarchy (not include the parent or children in other hierarchies).

KPH

The part I shared will make your contingency formula only execute on child rows. If your contingency formula is not working, or not doing what you want it to do, can you share it, and also say what it is doing that is wrong (error message or data problem)? Please also include a screen shot of any columns that the formula uses.

SO123

Hello KPH,

Here is the snapshot of my columns. The only with the a formula currently is the Contract Contingency Remaining column.

This column uses the following formula: =IF([Award or Amendment]@row = "Program Award", SUM(CHILDREN()), IF([Award or Amendment]@row = "PSA Award", MIN(CHILDREN()), IF([Award or Amendment]@row = "Contract Award", PARENT([Contract Contingency Approved]@row), IF([Award or Amendment]@row = "Contract Amendment", PARENT([Contract Contingency Approved]@row) + SUMIFS([Contract Contingency Released]:[Contract Contingency Released], Row:Row, @cell + 1 = Row@row) + [Contract Contingency Released]@row))))

I'm trying to adjust the last part of the formula, which applies to rows indicated as being a contract amendment. I have a helper row column which number the rows and are used in the formula for the cumulative total (1,2,3,4 etc.). What I have now seems to work with the exception of what I highlighted below. The formula is currently adding 50,000 in the contingency released column to the previous value in the column of 0. I want it to add the current draw to all previous draws in the same hierarchy (i.e. 50,000 and 50,000) instead rather than the one immediately above it. I tried to insert the COUNT(CHILDREN()=0) formula in different spots but keep getting errors saying it's not a valid operation or that it's unparasable (depending on where I put it). Would appreciate any suggestions you may have. Thanks!

SO123

Sorry forgot to tag you @KPH. Please see above

KPH

Hi @SO123

I have replicated your sheet. I have manually entered the data into the white cells and used =SUM(CHILDREN()) to complete the red, blue, and yellow rows. I have assumed the red is great grandparent, blue is grandparent, yellow is parent. I added some IDs to the primary column to show my structure. If this is not correct, please let me know.

Meanwhile, I will look at your existing formula and see what it is doing.

KPH

The formula is unparseable. This bit is causing a problem, specifically the criterion range and criteria in bold:

SUMIFS([Contract Contingency Released]:[Contract Contingency Released], Row:Row, @cell + 1 = Row@row)

If I take that out, the formula works, but is not doing what you want. What is is doing is:

• On Program Award rows (the red or blue) it puts the sum of the child rows in this column (the blue or the yellow).
• On the PSA Award rows (the yellow ones), it puts the lowest value from the child rows in this column.
• On the Contract Award row (the 1st white one in the set), it puts the Contract Contingency Approved from the parent row (the yellow PSA Award row).
• On the Contract Amendment row (the other white rows), it puts the Contract Contingency Approved from the parent row (the yellow PSA Award row) PLUS the Contract Contingency in the current row. This is where your SUMIFS was so you are trying to add something else here.

Can you see what in this list is not correct? Is it just that last part? The yellow part is bothering me as well!

Using your formula minus the bit that doesn't work, my data matches yours on each row except the final Amendment rows, and none of the red, blue, or yellow rows (I suspect because of an issue in the yellow rows).

I added another column to show what I think you want to appear (based on your screen shot and text). Can you confirm the figures in the What you want column are indeed what you want? Where this is blank I am assuming you want the figures in the Contract Contingency Remaining column.

I think I understand what you are trying to do in the white cells. To fix that, can you share the hierarchy column you mentioned that you have added?

SO123

Hello @KPH. The hierarchy I’m trying to achieve is the red being the great great grandparent, blue is great grandparent, yellow is grandparent, contract award lines are parent and the amendments are the children rows. So in the program award row I want to show the total contingency remaining from all the PSAs under that particular award. For example, this would be the total remaining from the first 3 PSAs for the first program award and the total remaining from the last PSA for the last program award. For the yellow PSA lines I want to show the total contract contingency remaining from all the contract awards under that PSA. In this example, there is only 1 contract per PSA but there could potentially be more in the future so I want to show the total remaining from all the contracts under that PSA. For the contract award lines, I want to show the total amount of that particular contract award remaining after all the amendments associated with that contract have been deducted.

And yes, the figures in the “What you want” column are what Im looking for in terms of the amendment lines and the blanks in that column would be the figures from the “Contract Contingency Remaining” column. So for the first contact award, for example, you would have the total contingency of 20,000 remaining after the first amendment as no funds were deducted. The second amendment would leave you with 15,000 and the third amendment would leave you with 5,000. Thanks again for your help with this!

KPH

Ok, got it. You said earlier “ I have a helper row column which number the rows and are used in the formula for the cumulative total (1,2,3,4 etc.).”. Can you take a screen shot with that in?

I am logging off now, but I will be back with a suggestion.

KPH

I don't know how your helper column is set up to count rows so am going to make a suggestion to include 2 new columns. To explain how this formula works and how I built it, I will go through it step by step.

Step 1 - Find the Cumulative Sum

To get started, I am going to ignore the different levels and just find the cumulative sum of one column. To get the cumulative Contract Contingency Released amount you would use a formula like this, where Row Number is a column that interatively counts your rows (usually an auto number column but you may have something else in place).

=SUMIFS([Contract Contingency Released]:[Contract Contingency Released], [Row Number]:[Row Number], @cell <= [Row Number]@row)

Here, the formula sums the values in the column Contract Contingency Released where the row number in the Row Number column is less than or equal to the current row number.

It looks like this. The formula is in the pale yellow column, the Row Number column is an auto number column.

This works beautifully for the first PSA Award. However, it does not reset to zero for each block. So, you will need an additional criterion in the SUMIFS to treat each PSA award (or sibling group) separately.

Step 2 - Give each sibling group an ID to identify the subsets

To give each row an ID that ties them to their siblings, I suggest adding another column. In that column, we can count the instances of "PSA Award" in the "Award of Amendment" column, and give each block a number. This formula will do that.

=COUNTIFS([Award or Amendment]:[Award or Amendment], "PSA Award", [Row Number]:[Row Number], @cell <= [Row Number]@row)

Step 3 - Calculate the Cumulative Total for a subset of the data

We can then extend our SUMIFS formal to refer to the PSA Group ID as well:

=SUMIFS([Contract Contingency Released]:[Contract Contingency Released], [Row Number]:[Row Number], @cell <= [Row Number]@row, [PSA Group]:[PSA Group], [PSA Group]@row)

This now creates a cumulative sum for each group separately.

Step 4 - Add the new formula to the existing one

This is the formula we have so far and we need to change the part in bold (which only adds the current row value to a calculation) to add the cumulative total.

=IF([Award or Amendment]@row = "Program Award", SUM(CHILDREN()), IF([Award or Amendment]@row = "PSA Award", MIN(CHILDREN()), IF([Award or Amendment]@row = "Contract Award", PARENT([Contract Contingency Approved]@row), IF([Award or Amendment]@row = "Contract Amendment", PARENT([Contract Contingency Approved]@row) + [Contract Contingency Released]@row))))

We replace that part in bold with the cumulative contract contingent that we calculated in step 3, like this:

=IF([Award or Amendment]@row = "Program Award", SUM(CHILDREN()), IF([Award or Amendment]@row = "PSA Award", MIN(CHILDREN()), IF([Award or Amendment]@row = "Contract Award", PARENT([Contract Contingency Approved]@row), IF([Award or Amendment]@row = "Contract Amendment", PARENT([Contract Contingency Approved]@row) + SUMIFS([Contract Contingency Released]:[Contract Contingency Released], [Row Number]:[Row Number], @cell <= [Row Number]@row, [PSA Group]:[PSA Group], [PSA Group]@row)))))

And now it looks like this (the formula is in the green column):

You can make the PSA Group formula a column formula and then hide it, and also hide Row Number.

Now, this formula does the following (change in bold):

• On Program Award rows (the red or blue) it puts the sum of the child rows in this column (the blue or the yellow).
• On the PSA Award rows (the yellow ones), it puts the lowest value from the child rows in this column.
• On the Contract Award row (the 1st white one in the set), it puts the Contract Contingency Approved from the parent row (the yellow PSA Award row).
• On the Contract Amendment row (the other white rows), it puts the Contract Contingency Approved from the parent row (the yellow PSA Award row) PLUS the Cumulative Contract Contingency Released up to the current row for that PSA Award.

Note - this only works if each grandparent is a PSA Award.

Do let me know how you get on.

SO123

Hello KPH. Thank you for the response. I tried doing what you said but I'm getting different numbers for the amendment lines for some reason. I used the formula you pasted above. =IF([Award or Amendment]@row = "Program Award", SUM(CHILDREN()), IF([Award or Amendment]@row = "PSA Award", MIN(CHILDREN()), IF([Award or Amendment]@row = "Contract Award", PARENT([Contract Contingency Approved]@row), IF([Award or Amendment]@row = "Contract Amendment", PARENT([Contract Contingency Approved]@row) + SUMIFS([Contract Contingency Released]:[Contract Contingency Released], [Row Number]:[Row Number], @cell <= [Row Number]@row, [PSA Group]:[PSA Group], [PSA Group]@row))))). Any thoughts as to why?

SO123