How do I calculate the sum of children rows only when their corresponding "checkbox" is unchecked?

monica16145

Hi,

I'm creating a sheet to track the progress of a photoshoot. I want my "Price2" cell to total the cost of children rows "Price2" and "Price3", etc. The cost will only ever be $47.40 or $0. (When the check mark is enabled, it indicates the photo has been taken, and therefore removes the associated cost). I want the total cost to appear in the parent row (ex: I want $90 to appear in "Price2". And If I checked off "Completion3", the cost would decrease to $47.50, and so on...).

The children rows in column "Price" have this function:

Hope someone can help...

Thanks in advance!

Kelly Moore

Hi @monica16145 

In your Parent row (Price2) you can use the formula =SUM(CHILDREN()) to accomplish what you asked for this cell.

Using an IF statement to filter between rows that have children (aka a Parent row) and those that do not have children, you can combine both of your formulas into one longer formula and have the same formula for every cell in the Price column.

=IF(COUNT(CHILDREN()) = 0, IF(Completion@row = 1, 0, 47.5), SUM(CHILDREN()))

This reads, if the row is a child row (ie, it has no children itself), then If Completion =1, make Price =0, otherwise Price =47.50. If the count of children doesn't equal zero, Sum all the children.

Does this work for you?

Kelly Moore

Hi @monica16145 

In your Parent row (Price2) you can use the formula =SUM(CHILDREN()) to accomplish what you asked for this cell.

Using an IF statement to filter between rows that have children (aka a Parent row) and those that do not have children, you can combine both of your formulas into one longer formula and have the same formula for every cell in the Price column.

=IF(COUNT(CHILDREN()) = 0, IF(Completion@row = 1, 0, 47.5), SUM(CHILDREN()))

This reads, if the row is a child row (ie, it has no children itself), then If Completion =1, make Price =0, otherwise Price =47.50. If the count of children doesn't equal zero, Sum all the children.

Does this work for you?

monica16145

@Kelly Moore

This worked perfectly! Thanks!