How to sum data for the last twelve months on a sheet that is updated frequently.

Wilson.springs

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 !

Paul Newcome

Try something like this:

=SUMIFS({$$}, {Dates}, @cell>= DATE(YEAR(MAX({Dates})) - 1, MONTH(MAX({Dates})), 1))

Wilson.springs

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?

Paul Newcome

Try just greater than instead of greater than or equal to.

Wilson.springs

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 ?

Paul Newcome

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))

Wilson.springs

The data is constantly being updated by other people so using a helper column with manual entry will not work

Paul Newcome

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: