Sum Values of Vlookup - SUMPRODUCT

[ktrasler]

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.

 

[ktrasler]

SOLVED: Sum Values of Vlookup - SUMPRODUCT

Hi guys

These are great solutions, exactly what I needed.

Really appreciate your help and sorry for confusing you all with the duplicate entry.

 

[ktrasler]

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

M.

Going with this one as its easiest for my brain to understand . great work guys

 

[Aladin Akyurek]

Going with this one as its easiest for my brain to understand . great work guys

I don't see much difference between sumif and sumifs though...