How to get my numbers to correctly add up?!

Ruvalcaba12

Hello everyone,

I am tasked with tracking budget moving forward and would like to be able to track overall budget but also budget for each POs.

I would like to have the formula to add the Children's Children's (if that makes sense) and ignore the Total of the PO.

This way I can have the total of the PO's in one line but have Smartsheet add the actual spend underneath each PO to have an actual total spend.

This would make my life so much easier so any help would be great.

AravindGP

Hi @Ruvalcaba12

You will need some helper columns if you're not getting the sum of spends by PO. The helper column is to identify the level with the formula

=COUNT(ANCESTORS())

This formula will give you a level of the hierarchy. You can then have a SUMIF formula,

=SUMIF([Helper column]:[Helper column], 3, [Invoice Total(s)]:[Invoice Total(s)])

AravindGP

Hi @Ruvalcaba12

You will need some helper columns if you're not getting the sum of spends by PO. The helper column is to identify the level with the formula

=COUNT(ANCESTORS())

This formula will give you a level of the hierarchy. You can then have a SUMIF formula,

=SUMIF([Helper column]:[Helper column], 3, [Invoice Total(s)]:[Invoice Total(s)])

Ruvalcaba12

https://community.smartsheet.com/discussion/comment/469939#Comment_469939

Where should the helper columns go? I'm unfamiliar with the helper columns.

AravindGP

Hi @Ruvalcaba12

Helper columns are just additional columns you add like any other column in your sheet. Name the column and use that column name in the formula to replace the text "Helper Column". As good practice, you can colour the column in any colour and hide it from view as it will not be manually updated by any user. Depending on your preference, you can have this column added in the left most or right most part of your sheet for easier hiding and unhiding