sumproduct and vlookup

[wujikwan]

I have following data and a range named exrate. I like to calculate the USD total value of all products. I can add a column to vlookup the exchange rate against USD and sumproduct the quantity, price, and exchange rate. However, is there a way to do without the extra column by just using sumproduct and say, vlookup? thank you.

colA colB colC colD

product quantity currency price
--------------------------------------------------
Prod01 10,000 AUD 10
Prod02 20,000 AUD 12
Prod03 3,000 CAD 10
Prod04 5,000 EUR 6
....

Total Value in USD: 9999999

EXRATE (range name)
colA colB

Currency Rate
--------------------
AUD 0.83
CAD 0.99
EUR 1.33
....

 

[barry houdini]

Hello wujikwan, welcome to MrExcel

One way is to use SUMIF to perform the lookup, e.g. with your first table (data) in A2:D5 and second table (conversion) in F2:G4 try this formula for total value

=SUMPRODUCT(B2:B5,D2:D5,SUMIF(F2:F4,C2:C5,G2:G4))

 

[wujikwan]

Thank you Barry, it works.

I didn't realize the sumif function could be used that way. I normally used one single value as criteria in the sumif function. It opens a new page to me. Thank you again.