# Column formula for only Children rows with Parent row =SUM(CHILDREN())

✭✭

I have PARENT and CHILDREN rows with the column where for PARENT row I indicate "TOTAL" in separate column. I would like to create Column Formula to calculate "Estimated Total" either for PARENT raw or for CHILDREN row automatically.

• ✭✭✭✭✭✭

Hi @Marina

I hope you're well and safe!

Try something like this.

```=IF(COUNT(CHILDREN(Task@row)) > 0, SUM(CHILDREN()),
[Estimated Units]@row * [Price per Unit]@row)
```

Did that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. 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.

• ✭✭✭✭✭✭

Hi @Marina

Hope you are fine, please try the following:

1- add a helper column to define if the row is children or parent and use the following formula and convert it to column format formula:

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

2- in the Estimated Total column use the following formula and convert it to column format formula:

```=IFERROR(IF(Level@row = 0, [Estimated Units]@row * [Price per Unit]@row, SUM(CHILDREN())), "")
```

the following screenshot shows the result

bassam.khalil2009@gmail.com

• ✭✭✭✭✭✭

Hi @Marina

I hope you're well and safe!

Try something like this.

```=IF(COUNT(CHILDREN(Task@row)) > 0, SUM(CHILDREN()),
[Estimated Units]@row * [Price per Unit]@row)
```

Did that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. 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.

• ✭✭
edited 07/16/21

@Andrée Starå It worked in test file but when I tried in Production, it didn't. Instead of "Task" column I have "Project Names [Primary]" column name. How would I use in the formula? I think I am missing correct punctuation. I need to calculate "Price per unit" and "Estimated hours" under children rows.

I even changed the name of the column to "Project Names" and used =IF(COUNT(CHILDREN([Project Names])@row)) > 0, SUM(CHILDREN()), [Estimated hours]@row * [Price per unit]@row)

but got an error #UNPARSEABLE

• ✭✭

@Andrée Starå actually I was able to figure out. You can ignore my comment above. This formula worked for me

=IF(COUNT(CHILDREN([Project Name \[Primary\]]@row)) > 0, SUM(CHILDREN()), [Estimated hours]@row * [Price per unit]@row)

• ✭✭✭✭✭✭

Excellent!

You're more than welcome!

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.

• ✭✭✭✭

The formula works great for me, however, I'm trying to make it a column formula and for some reason it won't let me (even though I'm using @row within the formula and not specific cells). =IF(COUNT(CHILDREN([Header Row]@row)) > 0, SUM(CHILDREN()), 40 - [FY22 W46 RH]@row - SUMIF({Person}, \$Resources@row, {FY22 W46}))

• Employee

There's an absolute reference in this formula (a \$ sign locking in a specific column and/or row).

In your case, it's before the "Resources" column:

=IF(COUNT(CHILDREN([Header Row]@row)) > 0, SUM(CHILDREN()), 40 - [FY22 W46 RH]@row - SUMIF({Person}, \$Resources@row, {FY22 W46}))

Remove that out and you should be good to go!

=IF(COUNT(CHILDREN([Header Row]@row)) > 0, SUM(CHILDREN()), 40 - [FY22 W46 RH]@row - SUMIF({Person}, Resources@row, {FY22 W46}))

Cheers,

Genevieve