=SUMPRODUCT((LOOKUP(A1:A3,{"","GBP","USD","YEN"},{ 0,1,1.97,243.2})),B1:B3)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"edge" wrote in message
...
if a colom has different currency format (like USD10 ,YEN 145.etc) and i
wish
to sum the value according to each currency format.
What should i do?
Advice please.
thanks