Vlookup/Match/Sum question

[jkeyes]

I tried searching for this but my queries didn't return what I needed...

Simply, I have an array 10 rows X 24 columns with column headers of the dates Jan-06 thru Dec-07 (as full dates). In the cells under these dates are just random numbers. I need to sum (by row) a select range of these cells based on a start date and X number of months out from that start date.

In a column next to the grid (Y) is the 'Start Date', e.g Jun-06, and in column Z is the number of months out I need to go, e.g. 6.

Col AA is where I would like to return, in this example, the values from F2:L2 (start date = Jun-06, and goes out 6 months from there to Dec-06).

Does this make sense? I'm fairly certain it involves some or all of the functions mentioned in the thread title, but my attempts so far have failed... Any and all help is greatly appreciated!

 

[Dan Waterloo]

The OFFSET function will also really help.

 

[Dan Waterloo]

In AA2:
=SUM(OFFSET(A2, 0, MATCH(Y2, A$1:X$1), 1, Z2))

Then copy down the AA column.

 

[Dan Waterloo]

Sorry. That was almost right but really should be
=SUM(OFFSET(A2, 0, MATCH(Y2, A$1:X$1) - 1, 1, Z2))

 

[Dan Waterloo]

By the way, thanks for describing your data so clearly.

 

[jkeyes]

Thanks for the reply Dan! I'm glad you thought I was clear, I was trying to be and it can be tough!

As for the formula, it works, except that it appears to be pulling the range I need plus one month on each end of the range. For example, based on my previous example, it's returning (summing) Jul-06 thru Jan-07.

It may be in how I converted to my actual data set, but I'm not seeing it... any other thoughts?

 

[jkeyes]

Nevermind, I see why. I just placed a '- 1' after the MATCH... thanks again!