How do I calculate the sum of children rows only when their corresponding "checkbox" is unchecked?
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!
Best Answer

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?
Answers

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?

This worked perfectly! Thanks!
Help Article Resources
Categories
Check out the Formula Handbook template!