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

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    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

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    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?

  • @Kelly Moore

    This worked perfectly! Thanks!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!