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.
Best 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
-
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.
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.9K Get Help
- 429 Global Discussions
- 147 Industry Talk
- 487 Announcements
- 5.2K Ideas & Feature Requests
- 86 Brandfolder
- 151 Just for fun
- 74 Community Job Board
- 497 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!