# Sum Values of Vlookup - SUMPRODUCT

#### ktrasler

##### Board Regular
Hi

I have a column of values, such as:

3
4
2
6
2

I need to look up these values from a key table, such as:

3 - 2
4 - 2
2 - 1
6 - 3
2 - 1

So the desired result is the sum of all the vlookup values. In the example a above the sum expected would be 9

Not sure whether this is a SUMIF array formula or a SUMPRODUCT. Would prefer SUMPRODUCT.

This works if the row size is the same, but it never is:

=SUMPRODUCT((G1:G5=A1:A5)*B1:B5)

for example this doesn't work =SUMPRODUCT((G1:G10=A1:A5)*B1:B5)

3
4
2
6
2
3
4
2
6
2

The desired result is 18.

Hope this makes sense.

Appreciate the help.

### Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

#### Canapone

##### Active Member
Hi,

a first appoach:

=SUMPRODUCT(VLOOKUP(N(IF({1},G1:G10)),A1:B5,2;0))

Hope it helps

Last edited:

#### Marcelo Branco

##### MrExcel MVP
ktrasler

Why the pair 2-1 appears twice in the table? Is this correct or just a typo?

M.

#### Canapone

##### Active Member
Hi again,

if the following is the final table

3 - 2
4 - 2
2 - 1
6 - 3
2 - 1

you could get same results using:

=SUMPRODUCT(CEILING(G1:G10/2,1))

Regards

-------------

Edit: sorry Marcelo Branco. I did not mean to overlap.

Last edited:

#### Marcelo Branco

##### MrExcel MVP

Edit: sorry Marcelo Branco. I did not mean to overlap.

No problem

I like your formula above (post #2)
=SUMPRODUCT(VLOOKUP(N(IF({1},G1:G10)),A1:B5,2;0))
Well done!

I was thinking in something like
=SUMPRODUCT(SUMIF(A1:A5,G1:G10,B1:B5))

but if the pair 2-1 (or any pair) may appear more than once, is necessary to use
=SUMPRODUCT(SUMIF(A1:A5,G1:G10,B1:B5)/COUNTIF(A1:A5,G1:G10))

M.

Last edited:

##### MrExcel MVP
Hi

I have a column of values, such as:

3
4
2
6
2

I need to look up these values from a key table, such as:

3 - 2
4 - 2
2 - 1
6 - 3
2 - 1

So the desired result is the sum of all the vlookup values. In the example a above the sum expected would be 9

Not sure whether this is a SUMIF array formula or a SUMPRODUCT. Would prefer SUMPRODUCT.

This works if the row size is the same, but it never is:

=SUMPRODUCT((G1:G5=A1:A5)*B1:B5)

for example this doesn't work =SUMPRODUCT((G1:G10=A1:A5)*B1:B5)

3
4
2
6
2
3
4
2
6
2

The desired result is 18.

Hope this makes sense.

Appreciate the help.

=sumproduct(sumifs(b1:b5,a1:a5,g1:g10))

#### Marcelo Branco

##### MrExcel MVP

=sumproduct(sumifs(b1:b5,a1:a5,g1:g10))

Probably you did not notice that the pair 2-1 appears twice in the table - see my post above.
Because of this duplication your formula yields 22, not 18 as desired.

M.

#### ktrasler

##### Board Regular
Hi

Apologies, that was a typo. each combination will only appear once.

#### Marcelo Branco

##### MrExcel MVP
Hi

Apologies, that was a typo. each combination will only appear once.

So, try
=SUMPRODUCT(SUMIF(A1:A5,G1:G10,B1:B5))

M.

#### ktrasler

##### Board Regular
Hi Canapone...

=SUMPRODUCT(CEILING(G1:G10/2,1))

Yeah that works too, thanks.

Can't get my head around this one, but doesn't matter as the others work just great.

=SUMPRODUCT(VLOOKUP(N(IF({1},G1:G10)),A1:B5,2;0))

Last edited:

Replies
1
Views
152
Replies
3
Views
313
Replies
3
Views
323
Replies
8
Views
340
Replies
12
Views
387

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,163,954
Messages
5,834,567
Members
430,297
Latest member
xa_gta

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