Calculate the sum for each month.
![fjohnson](https://us.v-cdn.net/6031209/uploads/defaultavatar/nWRMFRX6I99I6.jpg)
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?
Answers
-
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!!
-
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.
-
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))
-
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
-
Thank you Nick and Paul.
I'm working with both solutions. Appreciate your suggestions.
-
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.
-
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!
-
@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.
-
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!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.1K Get Help
- 430 Global Discussions
- 149 Industry Talk
- 489 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 154 Just for fun
- 74 Community Job Board
- 499 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!