# vlookup formula help

#### excel?

##### Board Regular
Is there a way to convert this formula to sum a range instead of the single column?

=VLOOKUP("Production Total",Input!\$A\$6:\$AK\$195,\$M\$3+1,FALSE)

\$M\$3 is a number generated based on the selection from a pull down box. If you select a date (10/4/06) from the Pull Down List, cell M\$3\$ returns "4", so the VLOOKUP Formula then looks in Column 5 and returns the "Production Total" for that day.

What I'm trying to do is have a pull down box by Week instead of by Day. So If I select Week 1, the formula returns the Sum of the "Production Total" from Column 2 thru 8 (10/1/06 - 10/7/06).

### Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

#### barry houdini

##### MrExcel MVP
If the week number is in A2 then you could try

=SUM(VLOOKUP("Production Total",input!\$A\$6:\$AK\$195,(\$A\$2-1)*7+{2,3,4,5,6,7,8},0))

confirmed with CTRL+SHIFT+ENTER

...although perhaps a simpler way would be to use

=SUM(OFFSET(INDEX(A6:A195,MATCH("production total",A6:A195,0)),,(A2-1)*7+1,,7))

which only requires ENTER

#### excel?

##### Board Regular
Thanks for the respoonse - can you tell me what is Cell A2 supposed to be? What is in this cell?

The Pull Down offers the user to choose "Week 1", "Week 2", "Week 3", and "Week 4". If "Week 1" is chosen then in Cell M3 is the number "1".

I do not see how to get your formula to Sum columns 2 thru 8 based on the user selecting "Week 1"

#### barry houdini

##### MrExcel MVP
Sorry, I assumed your week numbers would be determined by a different cell to M3. Just substitute M3 for A2, i.e.

=SUM(OFFSET(INDEX(\$A\$6:\$A\$195,MATCH("production total",\$A\$6:\$A\$195,0)),,(\$M\$3-1)*7+1,,7))

Replies
1
Views
444
Replies
3
Views
138
Replies
2
Views
493
Replies
1
Views
248
Replies
5
Views
244

1,171,579
Messages
5,876,285
Members
433,192
Latest member
butterexcel

### 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