I am trying to use the SUM and CHILDREN Function to subtract the actual cost vs the quoted amount

CJ1234

I am trying to use the SUM and CHILDREN Function to subtract the actual cost vs the quoted amount to determine the profit.

I have at the top of the page

Month

Week

Request

That is the hierarchy and I need to create a formula that will tell me the profit not only for each request but for the month as well.

Please help...

Paul Newcome

Try this:

=IF(COUNT(CHILDREN()) = 0, [Quoted Amount]@row - [Actual Cost]@row, SUM(CHILDREN()))

Paul Newcome

It is a 3 part formula.

First there is a part for child rows.

[Quoted Amount]@row - [Actual Cost]@row

Then there is a part for parent rows.

SUM(CHILDREN())

Then there is a part that says if it is a child row (it has no child rows of its own), then do the child row part, otherwise do the parent row part.

IF(COUNT(CHILDREN()) = 0, .........., ..........)

Paul Newcome

Are you able to provide some screenshots for context?

CJ1234

Paul Newcome

Try this:

=IF(COUNT(CHILDREN()) = 0, [Quoted Amount]@row - [Actual Cost]@row, SUM(CHILDREN()))

CJ1234

Oh great it worked! Thank you so much. Can you explain the formula to me. If it's not to much trouble.

Paul Newcome

It is a 3 part formula.

First there is a part for child rows.

[Quoted Amount]@row - [Actual Cost]@row

Then there is a part for parent rows.

SUM(CHILDREN())

Then there is a part that says if it is a child row (it has no child rows of its own), then do the child row part, otherwise do the parent row part.

IF(COUNT(CHILDREN()) = 0, .........., ..........)

CJ1234

Going forward, how can I better understand how to do these formulas? Is there a website that you recommend? I read what you put but is still doesn't make sense to me.

Paul Newcome

On the right side of the screen is a link to a formula handbook template. That should help get you started.