Calculate column running total from ONLY last 12 months

Options

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🦓

Best Answer

  • Leibel S
    Leibel S ✭✭✭✭✭✭
    Answer ✓
    Options

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

Answers

  • Leibel S
    Leibel S ✭✭✭✭✭✭
    Options

    @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
    Sofia Fernandez ✭✭✭✭
    Options

    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
    Leibel S ✭✭✭✭✭✭
    Answer ✓
    Options

    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
    Sofia Fernandez ✭✭✭✭
    Options

    Thanks that worked!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!