How to sum data for the last twelve months on a sheet that is updated frequently.
I have a sheet that collects monthly sales and I want to be able to sum the previous 12 months for a specific set of markets. For example, if I wanted to sum the last 12 months for Aiken and Bamberg how would I write a formula that would update as changes are made to the sheet. Each month new data is entered and I want a formula that I can use on a collector sheet to sum only the most recent 12 months as new data is entered. This formula has to be able to take only the previous 12 months as the sheet changes because the sheet is not static. Thanks in advance !
Answers
-
Try something like this:
=SUMIFS({$$}, {Dates}, @cell>= DATE(YEAR(MAX({Dates})) - 1, MONTH(MAX({Dates})), 1))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
I am using this SUMIFS({Abbeville}, {Date Entered 2}, @cell >= DATE(YEAR(MAX({Date Entered 2})) - 1, MONTH(MAX({Date Entered 2})), 1)) and getting the previous 13 months. Any idea how to get it to only do 12?
-
Try just greater than instead of greater than or equal to.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Thank you so much, that fixed it. Do you know the formula that I would use to display the last 12 months individually? Maybe an index collect ?
-
Individually I would suggest an INDEX/MATCH/LARGE combo.
=INDEX({$$}, MATCH(LARGE({Dates}, 1), {Dates}, 0))
The 1 in the LARGE function would pull the most recent. Changing that to a 2 will pull the second most recent, so on and so forth.
In your metrics sheet you can set up a basic helper column and manually enter the numbers 1 through 12 and then use a cell reference in place of the hardcoded number so that you can use the same formula for all 12.
1
2
3
4
5
etc.
=INDEX({$$}, MATCH(LARGE({Dates}, [Helper Column]@row), {Dates}, 0))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
The data is constantly being updated by other people so using a helper column with manual entry will not work
-
I meant the manually entered column would be on your metrics sheet. The 1 would pull the most recent. A new update comes in, that becomes the most recent, and the previous most recent becomes the 2nd most recent and is now pulled in on the row with the 2 manually entered.
Lets just say we are pulling 5 months (form entries on right, metrics sheet on left):
When I add a new entry with a new most recent date, you will see that the number is the far right will adjust to pull the 5 most recent entries as new forms are submitted.
And another entry:
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives