Sum of Parent and Child but also provide the Parent data when no Child present

Annick

I would like to calculate the Sum of the Parent and the Child data under Number of Compounds Tested, however I do not always have a child and only a Parent at times. I did create a Helper Cell Hierarchy but cant seem to find the correct formula. When there is a child, I do not want any sum calculated in that row as for this given project the information would be only featured on the row of the Parent.

What would be the best formula?

Paul Newcome

Are you able to provide some screenshots for more context? sample data is fine to show the structure and expected operation.

Annick

Here

Michelle Choate 2

Create a column labeled P. Column Formula =ANCESTORS([Vendor Study ID]@row). Create a second column labeled C. Column Formula =CHILDREN([Vendor Study ID]@row).

IF(P@row >0, "", IF(P@row = 0, SUM(CHILDREN([Number of Compounds to be Tested]@row + [Number of Compounds to be Tested]@row )

I use P & C on ALL of my sheets as helpers.