Sum of Last 12 months ended previous month?

[Katniss]

Hi, I'm trying to do a formula that counts the last 12 months sales data and then gives a total. But it needs to be a 'rolling total', so each month, I need to know what the total is for the PRIOR 12 months only ending the PREVIOUS month (Also, if the person hasn't has a full year of sales, I just need a total of what they have done so far)

Thanks!

Month Sales Sum of last 12 months Sales (ended previous month) Jan-10 $500.00 $0.00 Nothing as no sales prior to Jan 10 Feb-10 $200.00 $500.00 January only Mar-10 $100.00 $700.00 Sum Jan to Feb Apr-10 $800.00 $800.00 Sum Jan to Mar May-10 $1,000.00 $1,600.00 Sum Jan to Apr Jun-10 $200.00 $2,600.00 Sum Jan to May Jul-10 $0.00 $2,800.00 Sum Jan to June Aug-10 $40.00 $2,800.00 Sum Jan to July Sep-10 $500.00 $2,840.00 Sum Jan to Aug Oct-10 $900.00 $3,340.00 Sum Jan to Sept Nov-10 $1,500.00 $4,240.00 Sum Jan to Oct Dec-10 $100.00 $5,740.00 Sum Jan to Nov Jan-11 $200.00 $5,840.00 Sum Jan to Dec Feb-11 $20.00 $6,040.00 Sum Feb to Jan (last 12 months only) Mar-11 $0.00 $6,060.00 Sum March to Feb (last 12 months only) <colgroup><col><col><col><col><col></colgroup><tbody> </tbody> <colgroup><col><col><col><col></colgroup><tbody> </tbody>

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

 

[bosco_yip]

Try,

=IFERROR(SUM(OFFSET(C1,-1,,MAX(-12,-ROWS(A$1:A1)+1))),0)

p.s. the last 2 results appeared different, the above formula returned 5540 and 5360, please clarified.

Regards

 

[Katniss]

Thanks heaps! The formula worked perfectly