Calculate column running total from ONLY last 12 months

Sofia Fernandez

I want my columns to be like this..

Column A (Month & Year) Column B ($ in) and Column 3 (Running total from last twelve months)

Any help would be appreciated! Thanks🦓

Leibel S

Missed that see below formula:

=IF([Month & Year]@row <> "", SUMIFS([$ in]:[$ in], [Month & Year]:[Month & Year], IFERROR(@cell, "") > DATE(YEAR([Month & Year]@row) - 1, MONTH([Month & Year]@row), DAY([Month & Year]@row)), [ROW#]:[ROW#], <=[ROW#]@row))

If you know that you will always have the dates in order you can get rid of the extra columns mentioned previously (LINE-ID and ROW#) and you can use the below formula. With this formula it ignores where the row is on your list and only looks at the date. So if the dates are not in order the running total could look off.

=IF([Month & Year]@row <> "", SUMIFS([$ in]:[$ in], [Month & Year]:[Month & Year], AND(IFERROR(@cell, "") > DATE(YEAR([Month & Year]@row) - 1, MONTH([Month & Year]@row), DAY([Month & Year]@row)), @cell <= [Month & Year]@row)))

Leibel S

@Sofia Fernandez

Assuming your Column A is a date formatted column, then:

Add the below 2 helper columns:

1. "LINE-ID" : Auto Number Column
2. "ROW#" : Column Formula: "=MATCH([LINE-ID]@row, [LINE-ID]:[LINE-ID], 0)

In your column 3 use the below column formula:

=SUMIFS([$ in]:[$ in], [Month & Year]:[Month & Year], IFERROR(YEAR(@cell), "") = IFERROR(YEAR([Month & Year]@row), ""), [ROW#]:[ROW#], <=[ROW#]@row)

Sofia Fernandez

This is really helpful! And almost there...

I get this...

but what I want to happen is that Feb 21 contains 2536... since it has added the last twelve months not just the last calendar year...? Thoughts?

Leibel S

Missed that see below formula:

=IF([Month & Year]@row <> "", SUMIFS([$ in]:[$ in], [Month & Year]:[Month & Year], IFERROR(@cell, "") > DATE(YEAR([Month & Year]@row) - 1, MONTH([Month & Year]@row), DAY([Month & Year]@row)), [ROW#]:[ROW#], <=[ROW#]@row))

If you know that you will always have the dates in order you can get rid of the extra columns mentioned previously (LINE-ID and ROW#) and you can use the below formula. With this formula it ignores where the row is on your list and only looks at the date. So if the dates are not in order the running total could look off.

=IF([Month & Year]@row <> "", SUMIFS([$ in]:[$ in], [Month & Year]:[Month & Year], AND(IFERROR(@cell, "") > DATE(YEAR([Month & Year]@row) - 1, MONTH([Month & Year]@row), DAY([Month & Year]@row)), @cell <= [Month & Year]@row)))

Sofia Fernandez

Thanks that worked!