Combining Sumproduct with Vlookup

[jlogan223]

Hi All,

I am new to this forum, I have done a search but can not see any posts with the same issue, so here goes.

I have multiple documents each with 45+ sheets, 38 of which I need to get data from, I can not edit these sheets but I can add an additional one (summary Page). What I am trying to do is combine a Vlookup and a sum product in to a single cell on a summary page, If there are any easier ways of doing this please feel free to make a suggestion.

My Data:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Row/Column[/TD]
[TD]I[/TD]
[TD]AB[/TD]
[TD]AC[/TD]
[TD]AD[/TD]
[TD]AE[/TD]
[TD]AK[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Part[/TD]
[TD]Currency[/TD]
[TD]Type 1 Price[/TD]
[TD]Type 2 Price[/TD]
[TD]Type 3 Price[/TD]
[TD]Quantity required[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]a[/TD]
[TD]GBP[/TD]
[TD]1.50[/TD]
[TD]3[/TD]
[TD]6.25[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD]ab[/TD]
[TD]EUR[/TD]
[TD]4[/TD]
[TD]1[/TD]
[TD]0.5[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD]b[/TD]
[TD]USD[/TD]
[TD]3.30[/TD]
[TD]3.30[/TD]
[TD]7[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]23[/TD]
[TD]cg[/TD]
[TD]GBP[/TD]
[TD]12.90[/TD]
[TD]100[/TD]
[TD]156[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]24[/TD]
[TD]ef[/TD]
[TD]GBP[/TD]
[TD]0.90[/TD]
[TD]0.85[/TD]
[TD]12[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]..[/TD]
[TD]..[/TD]
[TD]..[/TD]
[TD]..[/TD]
[TD]..[/TD]
[TD]..[/TD]
[TD]..[/TD]
[/TR]
[TR]
[TD]..[/TD]
[TD]..[/TD]
[TD]..[/TD]
[TD]..[/TD]
[TD]..[/TD]
[TD]..[/TD]
[TD]..[/TD]
[/TR]
[TR]
[TD]..[/TD]
[TD]..[/TD]
[TD]..[/TD]
[TD]..[/TD]
[TD]..[/TD]
[TD]..[/TD]
[TD]..[/TD]
[/TR]
[TR]
[TD]100[/TD]
[TD]..[/TD]
[TD]..[/TD]
[TD]..[/TD]
[TD]..[/TD]
[TD]..[/TD]
[TD]..[/TD]
[/TR]
</tbody>[/TABLE]


What I need to be able to do is have one cell in a summary page that sum the following up:

What I need is a single formula for Type 1 price per sheet, so I need to do is:

multiple the quantity * the unit price *the currency (which needs to be looked up in another sheet),
then give me a sum for the complete sheet for Type 1 price in a single cell on a summary page, then the same for Type 2 and Type 3

so something like (but not) this:

Sumproduct=((AK20:AK100),AC20:AC100),Vlookup(AB20:AB100,'LOOKUPSHEET'!A1:B40,2,FALSE))

I hope this makes sense, If I have missed anything out please let me know.

Thanks,
Justin

 

[Domenic]

Assuming that each currency that occurs in Column AB is listed in the lookup table, try...

=SUMPRODUCT(AK20:AK100,AC20:AC100,SUMIF(LOOKUPSHEET!A1:A40,AB20:AB100,LOOKUPSHEET!B1:B40))

Hope this helps!

 

[jlogan223]

Assuming that each currency that occurs in Column AB is listed in the lookup table, try...

=SUMPRODUCT(AK20:AK100,AC20:AC100,SUMIF(LOOKUPSHEET!A1:A40,AB20:AB100,LOOKUPSHEET!B1:B40))

Hope this helps!

Thanks Domenic, That is almost exactly what I am looking for. I have however identified one issue, if the Currency appears more than once in the lookup table, it adds the conversation rates together, ie:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Country[/TD]
[TD]Currency Code[/TD]
[TD]Conversion rate[/TD]
[/TR]
[TR]
[TD]Germany[/TD]
[TD]EUR[/TD]
[TD]0.833[/TD]
[/TR]
[TR]
[TD]Great Britain [/TD]
[TD]GBP[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]United States[/TD]
[TD]USD[/TD]
[TD]0.571[/TD]
[/TR]
[TR]
[TD]France[/TD]
[TD]EUR[/TD]
[TD]0.833[/TD]
[/TR]
</tbody>[/TABLE]


This would then give me a multiplier of 1.66666, is there anyway around this, so that it finds the EUR value then just takes that one value instead of finding the rest and adding them up?

Regards,
Justin

 

[Domenic]

In that case, try...

=SUMPRODUCT(AK20:AK100,AC20:AC100,SUMIF(LOOKUPSHEET!A1:A40,AB20:AB100,LOOKUPSHEET!B1:B40)/COUNTIF(LOOKUPSHEET!A1:A40,AB20:AB100))

However, if you sort your lookup table by currency and in ascending order, the LOOKUP function can be used instead of SUMIF/COUNTIF. Using LOOKUP should be more efficient. So, for example, sort your lookup table as follows...

[TABLE="width: 500"]
<tbody>[TR]
[TD="class: xl65, width: 64"]Country[/TD]
[TD="class: xl65, width: 64"]Currency Code[/TD]
[TD="class: xl65, width: 64"]Conversion rate[/TD]
[/TR]
[TR]
[TD="class: xl65"]France[/TD]
[TD="class: xl65"]EUR[/TD]
[TD="class: xl65, align: right"]0.833[/TD]
[/TR]
[TR]
[TD="class: xl65"]Germany[/TD]
[TD="class: xl65"]EUR[/TD]
[TD="class: xl65, align: right"]0.833[/TD]
[/TR]
[TR]
[TD="class: xl65"]Great Britain[/TD]
[TD="class: xl65"]GBP[/TD]
[TD="class: xl65, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl65"]United States[/TD]
[TD="class: xl65"]USD[/TD]
[TD="class: xl65, align: right"]0.571[/TD]
[/TR]
</tbody>[/TABLE]

Then, if each item that occurs in Column AB is listed in the lookup table, try...

=SUMPRODUCT(AK20:AK100,AC20:AC100,LOOKUP(AB20:AB100,LOOKUPSHEET!B1:C40))

Otherwise, try...

=SUM(IF(ISNUMBER(MATCH(AB20:AB100,LOOKUPSHEET!B1:B40,0)),(AK20:AK100)*(AC20:AC100)*(LOOKUP(AB20:AB100,LOOKUPSHEET!B1:C40))))

...which needs to be confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!