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

Hi there! To sum values with different currency formats in Excel, you can use the SUMIF function along with a currency conversion rate. Here's how:

1. First, you'll need to determine the conversion rate for each currency. You can do this by searching online for the current exchange rate or by using a currency conversion website.
   
2. Once you have the conversion rates, create a new column in your spreadsheet where you'll convert all the values to a single currency. For example, if you want to convert all values to USD, create a new column and use a formula to convert each value to USD using the conversion rate. The formula would look something like this:
   Formula:

   =original value * conversion rate 

   .
   
3. Once you have all the values converted to a single currency, you can use the SUMIF function to sum the values based on their original currency format. For example, if you want to sum all values in USD, use the formula:
   Formula:

   =SUMIF(currency column, "USD", converted value column) 

   .
   
4. Repeat this process for each currency format you want to sum.

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

"edge" wrote:

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

=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

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

"edge" wrote:

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

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!

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!

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

--
HTH

Bob

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

"edge" wrote in message
...
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!

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

"edge" wrote:

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!

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 D1:Dn 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, DataPivot TableFinish
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.


--
Regards

Roger Govier


"edge" wrote in message
...
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!

Hi,All !

Thanks a lots