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))
-
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.
-
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))
-
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:
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives