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.

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

  • AravindGP
    AravindGP 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

  • AravindGP
    AravindGP 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

  • Ruvalcaba12
    Ruvalcaba12 ✭✭✭

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

  • AravindGP
    AravindGP 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!