Need help with proper formulas and functions

Steve_Mitchell

Hello Community,

I am brand new to SmartSheet (and not much experience in Excel either) but I was tasked with a problem to solve that I'm having trouble with and could use the community's expertise. Referencing the attached, I need to do 2 things...

1. I need to calculate the cells highlighted in yellow based upon status and weighting of the child tasks. So as an example in Parent 1 since Child 1 at 5% is Complete and Child 2 at 10% is Complete then the calculated value is 15%.
2. Each Parent has a value in a value column. I need a total of all the values where the entire parent (all child items) are complete. So in the example below the formula in the green adds Parent 2 value and Parent 4 values because they are 100% complete.

Any idea the best way to approach this and hopefully what formulas to use? I was thinking I may need a helper column for the Child weighting? Hopefully it could be hidden and still used in formulas?

I know it's a newbie question and a big ask but this is a critical item for me with a very short timeline. Thanks in advance for any help!!!

Hollie Green

You would need a helper column with the weight of each child row.

In the parent row you would enter a formula =SUMIF(CHILDREN([Status]1), "completed", CHILDREN([Weighting]1))

Enter the formula in each parent row changing the cell reference from 1 to the row number of the parent row you are entering the formula on.

For the other formula if you aren't going to have any value% in the child rows you can do =SUMIF([% complete]1:[% complete]25,"1",[Value %]1,[Value %]25)

You would replace the 1 and 25 in the formula with the starting number of your row and the last row you want it to add. I did it this way because you have the total(Parents Complete) in the same column so would give an error if you referenced the entire columns.

Steve_Mitchell

@Hollie Green Nevermind, I think I got it... Between Value 1 and Value 25 should be a semicolon instead of a comma - that fixed it!

Again, thank you so much for your help - I owe you one!!! Have an awesome day.

Hollie Green

You would need a helper column with the weight of each child row.

In the parent row you would enter a formula =SUMIF(CHILDREN([Status]1), "completed", CHILDREN([Weighting]1))

Enter the formula in each parent row changing the cell reference from 1 to the row number of the parent row you are entering the formula on.

For the other formula if you aren't going to have any value% in the child rows you can do =SUMIF([% complete]1:[% complete]25,"1",[Value %]1,[Value %]25)

You would replace the 1 and 25 in the formula with the starting number of your row and the last row you want it to add. I did it this way because you have the total(Parents Complete) in the same column so would give an error if you referenced the entire columns.

Steve_Mitchell

@Hollie Green Thank you so much!

The first part worked perfectly. The only bummer is I may have up to 400 Parent/Child instances to it'll be a bit of work to put the row #'s in for each. But it works!

On the second part I am getting an error #Incorrect Argument Set as you can see in the images below. I think I see what you are doing but don't see why the error. I'm assumingThoughts?

Steve_Mitchell

@Hollie Green Nevermind, I think I got it... Between Value 1 and Value 25 should be a semicolon instead of a comma - that fixed it!

Again, thank you so much for your help - I owe you one!!! Have an awesome day.

Hollie Green

@Steve_Mitchell Your very welcome. Sorry about the typo in the original formula.

Steve_Mitchell

@Hollie Green, after a little research and testing I tweaked the first formula so I wouldn't have to have it unique on each parent row. Do you see any reason why:

=SUMIF(CHILDREN(Status@row), "Complete", CHILDREN(Weighting@row)) wouldn't work instead of

=SUMIF(CHILDREN([Status]1), "completed", CHILDREN([Weighting]1))

so it could be used without change on each parent? It seems to work...?

Hollie Green

That's awesome I must have had something wrong in my test sheet I couldn't get it to work with the @row.