How can I use the SUM function in a column, but only add/account for numbers that are in child rows?
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!
Answers

Hi Barbara,
You’d need to use a socalled 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 semicolon 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 semicolon 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 by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå  Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com  E:andree@workbold.com  P: +46 (0)  72  510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Help Article Resources
Categories
Check out the Formula Handbook template!