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.

 

[Canapone]

Hi,

a first appoach:

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


Hope it helps

 

[Marcelo Branco]

ktrasler

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

M.

 

[Canapone]

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.

 

[Marcelo Branco]

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.

 

[Aladin Akyurek]

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]

=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]

Hi

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

 

[Marcelo Branco]

Hi

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

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

M.

 

[ktrasler]

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