Ooops, all the D5s should have been C1s

=SUM(OFFSET($A$2,0,MATCH(C1,1:1)-MIN(MATCH(C1,1:1),12),1,MIN(MATCH(C1,1:1),12)))

HTH,
Bernie
MS Excel MVP


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Bruce,

If you are certain that the date will have at least 12 columns of data to sum

=SUM(OFFSET($A$2,0,MATCH(C1,1:1)-12,1,12))

Otherwise, you need to make sure that you don't try to include the non-existent cells to the left
of column A:
=SUM(OFFSET($A$2,0,MATCH(D5,1:1)-MIN(MATCH(D5,1:1),12),1,MIN(MATCH(D5,1:1),12)))

Just enter the desired end date into C1.

HTH,
Bernie
MS Excel MVP


"BruceM via OfficeKB.com" <u54429@uwe wrote in message news:9f41476f35c7b@uwe...
I am using Excel 2003.

Cells A1 through X1 contain dates 01-Jan-2008, 01-Feb-2008 ... 01-Dec-2009.
They are formatted Jan-2008, Feb-2008 ... Dec. 2009. The point is that the
day does not matter.

Cells A2 through X2 contain numbers corresponding to the dates (actually, the
month and year represented by the date). Maybe something like 2, 1.5 ... 3.

I would like to specify a month/year and see the total for the cells in the
second row for that month\year and the 11 preceding it. For instance, July
2009 is the end of a twelve-month period August 2008 through July 2009.
These values are in cells H1:S1. I want to specify July-2009 (by typing it
into a cell (say C1), or selecting from month and year drop-down lists, or
whatever works best) and see the total for the numbers in cells H2:S2.

I realize the date range represented by cells H1:S1 is 01-Aug-2008 through 01-
Jul-2009 going by the actual cell values, but that is not of consequence here.
There is one number value for the entire month (in the cell directly below
the one containing the date value).

--
Message posted via http://www.officekb.com