How can I use the SUM function in a column, but only add/account for numbers that are in child rows?

Barbara Silva

Is there a formula that I could use to SUM numbers in a column, but NOT accounting for values that are in parent rows? The formula would go on a sheet summary; the reason not to account for values in parent rows, is to avoid duplication, since they are a reflection of the information in their child rows. Thank you!

Andrée Starå

Hi Barbara,

You’d need to use a so-called helper column that indicates if it’s a parent or child. (which Level)

In this example, it will show if it’s a child and then we’ll sum them.

Add a helper column called Level and the following formula.

This formula will show the levels for the parents and children will be blank.

I use this all the time for conditional formatting as well.

=IF(COUNT(CHILDREN([Task Name]@row)) > 0; COUNT(ANCESTORS()) + 1)

The same version but with the below changes for your and others convenience.

=IF(COUNT(CHILDREN([Task Name]@row)) > 0, COUNT(ANCESTORS()) + 1)

Depending on your country, you’ll need to exchange the comma to a period and the semi-colon to a comma.

For the SUM. Try something like this.

Try something like this.

=SUMIF(Level:Level; ""; Values:Values)

The same version but with the below changes for your and others convenience.

=SUMIF(Level:Level, "", Values:Values)

Depending on your country, you’ll need to exchange the comma to a period and the semi-colon to a comma.

Did it work?

I hope that helps!

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @WORK BOLD

✅Did my post help answer your question or solve your problem? Please help the Community bymarking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!