We have a board setup for our Marketing Budget and it has quickly become a big long list of expenditure. I am looking to tweak the current board so that the expenditure comes off the allocated budget.
Each budget item is catagorised (eg. Printing, Digital Marketing, Consultancy, Training) and there are items in these categories with their own budgets, for example, we have an overarching print budget, but there are also budgets for certain items like digital mailers, direct mail, exhibitions.
What we would like…
We would like items under the same category to come off the allocated budget. We could show the remaining budget in another column.
I can’t get the formula to add up the spends before taking that off a specific cell/column item. Please help!
Thanks for reaching out!
I would recommend slightly changing your board set-up in order to achieve this.
I would list each of your items as the budget items, and then utilise the subitems for each item that counts towards that budget.
This would look something like the example below:
You can then ‘roll up’ the subitems to have them show as a summary on your main level item:
This will then create a new column on your main level item that shows the summary of the subitems:
As the formula column only allows you to calculate horizontally, you can then add a formula column to subtract the amount spent from the budget.
As you add new subitems, this will automatically be added to the summary.
You can use the following formula to calculate the difference. Just be sure to swap out the column names so that they match your own!
This will leave you with the following:
I hope this helps! Remember to mark as solved if this answers your question
Thank you for the comprehensive post on how to solve my problem. I will have a look at this and come back.
Thank you so much. I have updated the board and it all appears to be working.
Are you able to make the Formula column used for amount remaining have a specific colour if it is over or under budget?