making a sheet with a 12month running total

**and01** · 04-23-2012, 04:27 AM

I have made a rolling spreadsheet with my incoming income for each consecutive month. I have a total for each month. What I want to do is to be able to calculate a total for the previous 12 months so that it updates, ie March to Feb, then April to March. Is there a formula that I can apply to a suitable cell please?

Thanks AJ

**Mallycat** · 04-23-2012, 04:43 AM

Yes, just use a sum formula. Point to 12 months of data for the sum formula. When you copy the formula, it will move to point to the rolling months (Provided you data is in consecutive cells)

**and01** · 04-23-2012, 05:41 AM

Originally Posted by Mallycat

Yes, just use a sum formula. Point to 12 months of data for the sum formula. When you copy the formula, it will move to point to the rolling months (Provided you data is in consecutive cells)

thanks for the quick reply. I have already managed to total a range of 12 separate months totals. I know I can manually do this for any particular range of months but I was wondering if there is anyway I can insert a formula into a cell to automatically total to previous 12months income?

**Mallycat** · 04-23-2012, 06:06 AM

Yes, you can do it automatically as I have described.I can't see what you are doing, but my best guess is you are using something like. A1+A2+A3..... Instead of what I meant which is sum(A1:A12)

**and01** · 04-23-2012, 07:03 AM

Hi this may be a duplicate post, thanks for your patience.

I have attached a demo file to show what I'm trying to do which is essentiallyto have the orange cells automatically total the previous 11 cells, so giving a constant tally of a range of 12 monthly income figures as new months are
added.

**Mallycat** · 04-24-2012, 03:59 AM

A few comments here.

1. Thanks for sharing the sheet. It is very difficult to give you feedback without a sample sheet.
2. I don't know what your motivation for laying out your spreadsheet this way. It is not how I would do it, nor how I think it should be done.
eg, Will you always have 4 amounts each month? Never 5 or more? The layout is for presentation purposes and this is not the best for spreadsheet management.
3. You say you want the total for the preceeding 12 months, however you only have 12 months data on the sheet. So there is only one cell that makes sense to have this formula, and that is cell E106. For the record, the formula is =SUM(D10:D106)
4. The way I would do it is create a table of data, and use the pivot table and summary tools to present your data. See my sample.

**and01** · 04-24-2012, 09:12 AM

Hi MC, thanks again.

My reason for wanting the running total is to keep track of the invoiced amount for each 12 month rolling period, there are normally around 15 invoices each month of vartying values. I understand what you are saying about the layout, in fact when I record invoices I do it just as you have shown in columns A & B, my exercise effort was just a crude attempt as I'm not an expert with Excel. Your sheet is interesting in that it doesn't seem to use formulas in the analytical table on the right. I would like to be able to lay mine out the same and add that 12month analysis also.

If poss would you explain how to do it?

Many thanks!