Calculate the sum for each month.

I have 3 columns, Cost, Milestone Status, and Actual Operational Date (M2)

I need to calculate/sum the total cost(revenue) based upon the Milestone Status(STARTED, or FINISHED), and specific for each month. In the case of the data indicated, I would need the results for November and January.

I then need to surface the results into a Dashboard.

What is the best strategy to accomplish this?

{F2ABC28B-BEBD-473B-9679-669A57325C20}.png

Find more posts tagged with

Accepted answers

All comments

NickStaffordPM

So, you will be able to capture all those metrics within the Sheet Summary. I would recommend setting up a column with the MONTH function tied to the created date. This will allow you to use a SUMIFS and reference both the status and ensure the month number cooresponds. For the sum by Milestone Status, a regular SUMIF should be sufficent. You will then be able to visualize this information in a Sheet Summary Report on the Dashboard, or as individual metrics, depending on how you want it visualized.

Let me know if you have any other questions!!

fjohnson

Okay, I need a bit more detail on what you have proposed.

As I understand, first, I need to create a new column in my Sheet and utilize the MONTH function to parse out the Month from my Actual Operational Date (M2) column. I have created the new column and added the below formula.

Formula used in new column.

=MONTH([Actual Operational Date(M2)]@row)

Then, I'm a bit unsure as to my next step. I think you are suggesting that I perform some calculations within the Sheet Summary, then create a new Sheet Summary Report referencing the results in the Sheet Summary.

I'm not clear on how I perform the calculations in the Sheet Summary.

Paul Newcome

I would suggest a second sheet instead of Sheet Summary fields if you are wanting to put this on a dashboard. You also do not need to insert any additional columns into the source sheet. You can reference the month and year from the dates directly.

=SUMIFS({Cost}, {Status}, @cell = "STARTED", {Date}, AND(IFERROR(MONTH(@cell), 0) = 11, IFERROR(YEAR(@cell), 0) = 2025))

NickStaffordPM

Paul's comment above will definitely work (I always like reading Paul's answers because I consider him one of the most Smartsheet-savy people in this community and I always learn a new approach.. plus his synthax is beautiful to read):

The way I was mentioning, you can find the sheet summary and add a metric like in the following image. You would then use the formula

JAN

=SUMIFS({Cost}:{Cost},{MONTH Number}:{Month Number},"1", {Milestone Status}:{Milestone Status},"FINISHED")

FEB

=SUMIFS({Cost},{MONTH Number},"2", {Milestone Status},"FINISHED")

MARCH

=SUMIFS({Cost},{MONTH Number},"3", {Milestone Status},"FINISHED")

ETC…. for all 12 months

You could then create a Sheet Summary Repot (Create a report, then select sheet summary option). Select the sheet you set up the sheet summary formulas on, then add it to a dashbboard as either a report, chart or metric table and display as you need

fjohnson

Thank you Nick and Paul.

I'm working with both solutions. Appreciate your suggestions.

Paul Newcome

Happy to help. 👍️

Technically dropping formula into sheet summary fields will work, but I tend to avoid that. I have found working with cross sheet references in sheet summary fields to be buggy to the point where I will write the formula in a cell in the main sheet and then move it to the summary field. I also do a lot of Control Center work which has it's own limitations and requirements. There is already a metrics / metadata sheet in a vertical format, and Control Center Global Updates can't touch sheet summary fields.

In one-off instances, dropping metrics into sheet summary fields should be fine, but I would certainly try to avoid it when possible if scaling, replication, or regular modifications (such as rolling 6 months or "current year" labels for your fields) are required.

NickStaffordPM

One more thing…

If you do go the Sheet summary way, you may want to layer in a YEAR column as well (including in the SUMIFs), so year over year you are only summing what was from that year's month, and not past years in the same month. Just a thought.. This will help scale over time.

Happy sheeting!

Paul Newcome

@NickStaffordPM You can use the criteria from my formula in same sheet formulas as well to avoid needing the extra columns.

=SUMIFS(Cost:Cost, Status:Status, @cell = "STARTED", Date:Date, AND(IFERROR(MONTH(@cell), 0) = 11, IFERROR(YEAR(@cell), 0) = 2025))

The other benefit to a second sheet is you can put dates in a column such as the first of each month (formulas can automate that part as well, but that's a separate piece), and then you can use cell references to create a more dynamic formula that can be applied at the column level.

=SUMIFS({Cost}, {Status}, @cell = "STARTED", {Date}, AND(IFERROR(MONTH(@cell), 0) = MONTH([Date Column]@row), IFERROR(YEAR(@cell), 0) = YEAR([Date Column]@row)))

Taking that one step further can also help you create a formula for a "Labels" column for charting something like a rolling 6 months by outputting something like "Jan 25", "Feb 25", so on and so forth based on the dates in [Date Column] on your second sheet, so now your chart is completely dynamic and should require zero intervention for the life of the project.

NickStaffordPM

Great point Paul - I was boxing myself in creating new columns but its way easier to just embedd in the formula…

Now I am off to fix up abunch of my Smartsheet systems LOL

Thanks!

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.