How do i sum values with Different currency Format?

[Guest]

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

 

[Guest]

You first need to convert them to the same currency and then do your sums.
You can import live currency convertersion rates from many sites including
the one below.

http://www.x-rates.com/

Use Data|import external data|new web query
paste in the link above and import the conversion table.

Mike

 

[Bob Phillips]

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

 

[Guest]

Are you attempting to SUM the whole column --- If so then refer to Mike H's
post

from what I read in your post you want to return the SUM for each currency
type
ie Sum(USD), SUM(Yen) etc........

If this is the case then explore the built in SUMIF worksheet function.

Regards
Steve

 

[Guest]

Hi,Mike!
Thanks for your solution.
But actually i need to know the total value according to each currency.
like what is the total sum for USD,YEN,Etc
It is all in a colom.
kindly advice please.

Thanks n Regards!

 

[Guest]

Hi,Steve!

Thanks for your advice.
I have tried 'sumif' but i don't know what is the criteria for that function.
Because the currency in each cell is currency format not like the value as
we type in manually.
kindly advice please

Thanks and regards!

 

[Bob Phillips]

=SUMIF(A2:A20,"USD",B2:"))*usd_rate

--
HTH

Bob

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

 

[Guest]

Hi

Only way I can see to solve this is to create a helper Column
for eg:
Column A = Helper column
Column B = range to Sum

A1 - USD B1 = $10
A2 - YEN B2 = ¥10

Formula to sum USD = SUMIF(A1:A2,"USD",B1:B2) = 10
Formula to sum USD = SUMIF(A1:A2,"YEN",B1:B2) =10

At the moment this is the only simple solution that I can think of, but I am
sure there is a way.

HTH

 

[Roger Govier]

Hi

Another alternative to Steve's suggestion, again using a helper column.
Assuming your data is in column A, enter in cell B1
=A1*(CELL("format",A1)=CELL("Format",$C$1))
and copy down as far as required.

In column D, in cells D1n format the cells as per the various
currencies you use, and enter a 1 in each cell so you have
£1.00, $1.00, ?1.00 etc.

Now, copy the Currency symbol you want to C1, and SUM(B:B) will give the
total value for that currency

A better alternative would be to just enter in column B
=CELL("format",A1)
and copy down.
Insert a new row 1 as a header, with Value in A1 and Currency in B1
Mark columns A and B, Data>Pivot Table>Finish
On the new sheet created, drag Currency to the row area and drag Value
to the data area.
You can overtype the cell contents of the "Currency" showing in Column
A, with GBP £, USD $, EUR ? or whatever you wish and you will see
alongside the total value for each currency.

 

[Guest]

Hi,All !

Thanks a lots