Formulas and Functions

Formulas and Functions

Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

How to get my numbers to correctly add up?!

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.

image.png

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.

Tags:

Best Answer

  • Community Champion
    Answer ✓

    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)])

    Thanks,

    Aravind GP| Delivery Manager

    Atturra Data & Integration

    M: +61493337445

    E:Aravind.GP@atturra.com

    W: www.atturra.com

Answers

  • Community Champion
    Answer ✓

    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)])

    Thanks,

    Aravind GP| Delivery Manager

    Atturra Data & Integration

    M: +61493337445

    E:Aravind.GP@atturra.com

    W: www.atturra.com

  • ✭✭✭

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

  • Community Champion

    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

    Thanks,

    Aravind GP| Delivery Manager

    Atturra Data & Integration

    M: +61493337445

    E:Aravind.GP@atturra.com

    W: www.atturra.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!

Trending in Formulas and Functions