Formula based on variable range

[BruceM via OfficeKB.com]

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

 

[Bernie Deitrick]

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

 

[Bernie Deitrick]

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

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]

Thanks! I gave a somewhat simplified example, but I did some experiments and
can see how MATCH and OFFSET will get me to where I need to go. The problem
was I didn't know about the existence of those functions.

I did have some problems getting MATCH to match a date value unless I
referred to a specific cell, or used the DATE or DATEVALUE function (probably
some others, too). What I'm actually doing is having the user select the
month from one drop-down box and the year from another, then combining those
values using the Date function, with a 1 added for the day.

As I said, I think this puts me on track, but now other things need to be
done before I get back to this project, so I may not be able to work out the
details right away. Thanks again for pointing me in what seems to be the
right direction.

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

Bruce,

[quoted text clipped - 32 lines]