Previous 11 + Current

  • Thread starter Thread starter Mickey
  • Start date Start date
M

Mickey

Hi,
I have a row of cells in which data is added monthly, I'd like to sum the
the most recent 12 months without the need to amend the formula each month,
any ideas please?

Thx, Mickey
 
=SUM(LARGE(IF(1:1<>"",COLUMN(1:1)),{1,2,3,4,5,6,7,8,9,10,11,12}))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Mickey,

Try the following array formula. It assumes your data is in row 3 starting
in column A. Change the 3's to your row number.

=SUM(OFFSET(INDIRECT(ADDRESS(3,MAX(COLUMN(3:3)*(3:3<>""))-11)),0,0,1,12))

Since this is an array formula, you must press CTRL+SHIFT+ENTER rather than
just ENTER when you first enter the formula and whenever you edit it later.
If you do this properly, Excel will display the formula enclosed in curly
braces {}. See http://www.cpearson.com/excel/array.htm for more information
about array formulas.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting LLC
www.cpearson.com
(email on the web site)
 
try this where col I is the monthly date such as 1/1/2006 and col J is the
amount to sum

=SUMPRODUCT((I1:I21>=TODAY()-365)*J1:J21)
 
Fantastic,
Many thanks for all of your advice, it is greatly appreciated.

Best Wishes,
Mickey
 
Back
Top