Remaining Budget Column Formula

I have an intake form where we receive campaign budgets. We are trying to track when our campaigns as a whole start spending over our expected Overall Budget. I need a column formula that can calculate the remaining budget when a new campaign is added. Currently, the intake form is set up to have new campaigns be added at the top.

Below is a screenshot of an Excel version of this where the budget cell is just =[new budget] - [previous remaining budget] and the first row (CPN-1) is =[overall budget] - [campaign budget CPN-1]

Any formulas I saw that were similar included excel functions like ROW or OFFSET which Smartsheet doesn't have. I would love any advice or recommendations on how to solve this!

Find more posts tagged with

Accepted answers

Carson Penticuff

I am making a few assumptions here... if those are incorrect, this would need to be tweaked somewhat:

Your sheet uses the same consistent ID scheme as in your screenshot. Missing IDs will not cause an issue, but different formats (even including spaces) will throw it off
"Overall Budget" is stored in a field in the sheet summary. This doesn't have to be the case... it could be setup to work other ways.

Give this a try and see if it does what you want:

=[Overall Budget]# - SUMIFS([Campaign Budget]:[Campaign Budget], [Campaign ID]:[Campaign ID], VALUE(RIGHT(@cell, LEN(@cell) - 4)) <= VALUE(RIGHT([Campaign ID]@row, LEN([Campaign ID]@row) - 4)))

All comments

Carson Penticuff

I am making a few assumptions here... if those are incorrect, this would need to be tweaked somewhat:

Your sheet uses the same consistent ID scheme as in your screenshot. Missing IDs will not cause an issue, but different formats (even including spaces) will throw it off
"Overall Budget" is stored in a field in the sheet summary. This doesn't have to be the case... it could be setup to work other ways.

Give this a try and see if it does what you want:

=[Overall Budget]# - SUMIFS([Campaign Budget]:[Campaign Budget], [Campaign ID]:[Campaign ID], VALUE(RIGHT(@cell, LEN(@cell) - 4)) <= VALUE(RIGHT([Campaign ID]@row, LEN([Campaign ID]@row) - 4)))

aplunkett02

@Carson Penticuff

You are a rockstar! That worked! Thank you so much!!

Carson Penticuff

Happy to help!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.