What equation should I use for a floating Quarterly budget total

Hi Smartsheet community,

I am hoping to get some help with solving for a current and next quarterly budget for each of my projects. I am hoping to make it so that the next quarter and current quarter change as the next quarter turns into the current quarter. I am adding it to my project dashboard which is the photo that follows.

Screenshot 2025-05-02 at 11.31.53 AM.png

I am hoping to do this for each project on an individual basis. Currently the data is pulled from each individual project plan then taked to a portfolio metadata page. Is there a quick and easy way to do all of my current projects or will I have to do the equation on an individual basis?

Thank you guys for the help!

Find more posts tagged with

Accepted answers

Adam Murphy

Not sure how others solve for this type of thing, but my solution has been to create what I call relative date columns that simply look at a date field and determine if it is "This QTR", "Last QTR" or "Out of Range" (I don't need future for what i'm reporting, but it would be simple to add). They are just nested IF statements. I do this for Year, QTR, Month and Week, and will then use a combination of filters in a report to get the data I want (e.g. "This Year" and "This QTR"). Sounds like you could add these columns to your metadata sheet and then report against that. Hope that helps.

Adam Murphy

Rather than putting in all those references I would have added a helper column to determine if a row is a child, then used that as part of the criteria for SUMIFS. Would be a whole lot cleaner that way.

All comments

Adam Murphy

Phipps Lawrence

Hi Everyone,

So update I was able to figure out the equation however it will not work and says Unparseable or Invalid Operation Error when I try it in two different formats. I basically need to sum up all the children and only show an anticipated spend over the next 90 days. The equation I am using is =SUMIFS({Project Plan Range 3}{Project Plan Range 6}{Project Plan Range 7}{Project Plan Range 9}{Project Plan Range 8}{Project Plan Range 14}{Project Plan Range 15}{Project Plan Range 16}{Project Plan Range 17}{Project Plan Range 18}{Project Plan Range 19}{Project Plan Range 20}{Project Plan Range 21}{Project Plan Range 22}{Project Plan Range 23}{Project Plan Range 24},{Project Plan Range 25}{Project Plan Range 26}{Project Plan Range 27}{Project Plan Range 28}{Project Plan Range 29}{Project Plan Range 10}{Project Plan Range 30}{Project Plan Range 31}{Project Plan Range 32}{Project Plan Range 33}{Project Plan Range 34}{Project Plan Range 35}{Project Plan Range 36}{Project Plan Range 37}{Project Plan Range 38}{Project Plan Range 39}{Project Plan Range 40},>=TODAY(),Project Plan Range 25}{Project Plan Range 26}{Project Plan Range 27}{Project Plan Range 28}{Project Plan Range 29}{Project Plan Range 10}{Project Plan Range 30}{Project Plan Range 31}{Project Plan Range 32}{Project Plan Range 33}{Project Plan Range 34}{Project Plan Range 35}{Project Plan Range 36}{Project Plan Range 37}{Project Plan Range 38}{Project Plan Range 39}{Project Plan Range 40},<=TODAY()+90)).

Reason it is so long is because I do not want to sum up the parent cells so I have to only do the children. When I only sum the children it says Unparseable but when I sum up the entire columns it shows Invalid Operations Error. I have attached a screenshot of a sample amount of cells I am looking to add in. Any help would be greatly appreciated because I do not know why it is not working.

Adam Murphy

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.