# Vlookup + Sum Combination

#### Siepe1990

##### New Member
Hello Fellow-Excel users,

I am trying to get something to work, let me explain what I am trying:

 Prod Nr Jan Feb Mar Apr 100.01 100 150 0 20 200.05 200 200 250 100

<tbody>
</tbody>

The table above contains sales volumes per month on a product level.

On another sheet, I want to vlookup the sum of the last 3 months.
So if I vlookup 100.01 I want the outcome to be 150+0+20 = 170.
If I vlookup 200.05 I want the outcome to be 200+250+100 = 550.

Is there any easy way this can be achieved?

### Excel Facts

Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

#### Jonmo1

##### MrExcel MVP
Will the data always be filled from left to right?
Meaning, there will not be a value under Apr, without values already under Jan Feb and March?

#### etaf

##### Well-known Member
with vlookup you would just add then together

so
vlookup(cell,range, 3, false) + vlookup(cell,range, 4, false) + vlookup(cell,range, 5, false)

are you adding to the columns each month and you want to automatically select the last three entries

#### Siepe1990

##### New Member
Will the data always be filled from left to right?
Meaning, there will not be a value under Apr, without values already under Jan Feb and March?

Yes, that's right, always month by month.

#### Siepe1990

##### New Member
with vlookup you would just add then together

so
vlookup(cell,range, 3, false) + vlookup(cell,range, 4, false) + vlookup(cell,range, 5, false)

are you adding to the columns each month and you want to automatically select the last three entries

Yeah, that'll work, but is a very long formula (especially when more months are added, this is just a simple example of course, my real data set is bigger)...

#### pgc01

##### MrExcel MVP
Hi
Welcome to the board

If the table with the totals has the values in the same order as the source table, try in Sheet4!B2:

=SUM(LOOKUP(2,1/(Sheet3!B2:M2<>""),OFFSET(Sheet3!B2,0,COLUMN(Sheet3!B2:M2)-COLUMN(Sheet3!B2)-2,1,3)))

This is an array formula, you have to confirm it with CTRL-SHIFT-ENTER and not just ENTER.

Copy down

#### Jonmo1

##### MrExcel MVP
Try this assuming the table you posted begins in A1 (A1 = Prod Nr)

=SUM(OFFSET(INDEX(\$2:\$100,MATCH(\$K1,\$A\$2:\$A\$100,0),MAX(2,COUNTA(OFFSET(\$1:\$1,MATCH(\$K1,\$A\$2:\$A\$100,0),0))-2)),0,0,1,3))

K1 = the lookup value of 100.01

Last edited:

Replies
3
Views
80
Replies
1
Views
466
Replies
6
Views
194
Replies
7
Views
102
Replies
13
Views
471

1,172,166
Messages
5,879,423
Members
433,429
Latest member
Ever

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