Sum Values of Vlookup - SUMPRODUCT

ktrasler

Board Regular
Joined
May 21, 2007
Messages
50
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

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

Canapone

Active Member
Joined
May 10, 2007
Messages
463
Hi,

a first appoach:

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


Hope it helps
 
Last edited:

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,510
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
ktrasler

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

M.
 

Canapone

Active Member
Joined
May 10, 2007
Messages
463
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
Joined
Aug 23, 2010
Messages
16,510
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

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

That's why i asked OP about the duplication

M.
 
Last edited:

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,203
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
Joined
Aug 23, 2010
Messages
16,510
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

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


Aladin,

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
Joined
May 21, 2007
Messages
50
Hi

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

ktrasler

Board Regular
Joined
May 21, 2007
Messages
50
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:

Watch MrExcel Video

Forum statistics

Threads
1,129,349
Messages
5,635,774
Members
416,880
Latest member
kyleswanson

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top