Rolling Totals

kent.robinson

Trying to create a formula that gives me rolling totals for each of the last 3 months. So in December, it would give me totals for nov, oct, sep. And in Jan it would give me dec, nov, oct and so on. I can get previous month, but run into trouble when i cross into a new year. Any advice?

Mark.poole

@kent.robinson This is not mine so I wont take credit for it. but try this>

Assumptions:

• You have a column named Date containing the dates of the entries.
• You have a column named Value containing the numeric values to sum.
• Create 3 Helper columns

Steps:

1. Use the following formulas:
   • Last Month?
   • =IF(YEAR(TODAY() - 30) = YEAR([Date]@row) AND MONTH(TODAY() - 30) = MONTH([Date]@row), 1, 0)
   • 2 Months Ago?
   • =IF(YEAR(TODAY() - 60) = YEAR([Date]@row) AND MONTH(TODAY() - 60) = MONTH([Date]@row), 1, 0)
   • 3 Months Ago?
   • =IF(YEAR(TODAY() - 90) = YEAR([Date]@row) AND MONTH(TODAY() - 90) = MONTH([Date]@row), 1, 0)
     
2. =SUMIF([Last Month?]:[Last Month?], 1, [Value]:[Value]) + SUMIF([2 Months Ago?]:[2 Months Ago?], 1, [Value]:[Value]) + SUMIF([3 Months Ago?]:[3 Months Ago?], 1, [Value]:[Value])
   
3. =IF(AND([Date]@row >= DATE(YEAR(TODAY()), MONTH(TODAY()) - 3, 1), [Date]@row < DATE(YEAR(TODAY()), MONTH(TODAY()), 1)), SUMIF([Date]:[Date], AND([Date] >= DATE(YEAR(TODAY()), MONTH(TODAY()) - 3, 1), [Date] < DATE(YEAR(TODAY()), MONTH(TODAY()), 1)), [Value]:[Value]), 0)
   
   Replace Date and Value with your column names.

Explanation:

• Handling Year Transitions: The DATE(YEAR(), MONTH() - X, 1) function adjusts for months that wrap around into the previous year.
• Dynamic Rolling Totals: The formulas adjust dynamically based on the current date.

kent.robinson

assumptions are correct.

Does your solution mean adding new columns to an existing sheet (step 1)

forgot to mention that i am trying to do this as a sheet summary filter.

kent.robinson

i am getting an unparseable error when entering the formula; after updating date to my field name. is the and in the correct space?

kent.robinson

and how do you account for months that do not have 30 days (Feb 28 or 29, Jan, Mar, May, Jul, Aug, Oct, Dec 31)

Mark.poole

@kent.robinson Give me a few. I pulled that solution pretty quick. Ill come up with one for you.

Mark.poole

@kent.robinson

try

=IF(MONTH(TODAY()) = 1, IF(AND(YEAR(TODAY()) -1, MONTH(DATE@row) = 12, SUMIFS(VALUE@Row, DATE@row, MONTH(Date@row) = 12), SUMIFS(VALUE@Row, DATE@row, MONTH(TODAY()) -1))))

To see if it properly rolls over for you to last month. At which point you can do this formula 2 more times. Changing 12 to 11 and 1 to 2. then changing to 10 and 3

kent.robinson

thank you for your help Mark. In your most recent solution what is value@row to represent?

kent.robinson

mark

following up. what does the value@row represent?

Paul Newcome

If you are planning to put this into a sheet summary field and have it automatically sum values from one column based on the dates in another column as long as they are within the last three calendar months from today's date, it would look something like this:

=SUMIFS([Column To Sum]:[Column To Sum], [Date Column]:[Date Column], AND(@cell >= IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) - 3, 1), DATE(YEAR(TODAY()) - 1, MONTH(TODAY()) + 9, 1)), @cell <= DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 1)