Need help with using SUM/SUMIF function along with Hierarchy

NishaTKD · 05/12/22

Could someone please help by pointing out what is wrong with this function? I would like row 1 to show the total of row 2 and row 7. (i.e. when the Hierarchy level is 1)

=SUMIF([PO Amount]1:[PO Amount]10, [Project Hierarchy]@row, 1)

Here is the screenshot of my Smartsheet data source

I have a function in row 2 and row 7 that works just fine. it is =SUM(CHILDREN([PO Amount]7:[PO Amount]9)).

Thanks in advance.

Ameya Athalye · 05/12/22

use =SUM(CHILDREN()) formula on parent row and it should show you the desired sums.

Mike TV · 05/12/22

@NishaTKD

=SUMIF([PO Amount]1:[PO Amount]10, [Project Hierarchy]@row = 1)

Such a little thing. needs to be = 1 instead of , 1 at the end.

Ameya Athalye · 05/12/22

use =SUM(CHILDREN()) formula on parent row and it should show you the desired sums.

Mike TV · 05/12/22

@NishaTKD

=SUMIF([PO Amount]1:[PO Amount]10, [Project Hierarchy]@row = 1)

Such a little thing. needs to be = 1 instead of , 1 at the end.

NishaTKD · 05/12/22

Thank you @Ameya Athalye. It worked! :-)

Ameya Athalye · 05/12/22

Awesome!

NishaTKD · 05/12/22

Thank you @Matt C. for pointing out the error :-) I corrected the function but got CIRCULAR REFERENCE error. Perhaps I had used other functions in the same row. However I was able to get to the desired state just by using =SUM(CHILDREN()).

Thank so much for prompt inputs!

Formulas and Functions

Formulas and Functions

Need help with using SUM/SUMIF function along with Hierarchy

Best Answers

Answers

Help Article Resources

Quick Links

Categories

Trending in Formulas and Functions

Formula - use % complete to determine whether in progress

Formula error

TRYING TO FIND THE ERROR IN THIS FORMULA