# Vlookup/Match/Sum question

#### jkeyes

##### Active Member
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!

### Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
The OFFSET function will also really help.

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

Then copy down the AA column.

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

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

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?

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

Replies
4
Views
649
Replies
16
Views
2K
Replies
2
Views
406
Replies
5
Views
274
Replies
5
Views
241

1,219,581
Messages
6,149,119
Members
450,861
Latest member
metcala

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back