SUM DIFFERENT CURRENCIES

[Irfan]

I have a worksheet containing different suppliers list with their products
and their amounts in different currencies (formatted as numbers with
different currencies). as mentioned below:

A B C D E
Date Supplier Product Qty Amount
27/01/2008 XYZ ABC 20 £23.00
27/01/2008 OPL Buns 10 £22.00
29/01/2008 OBS Deca 3 $3.00
29/01/2008 OPL Deca 5 $3.00
29/01/2008 OBS Fil 3 $3.00
27/01/2008 XYZ ABC 20 $3.00

What i want to sum up the currencies (dollars and pounds) separately.

Please help.

 

[NoodNutt]

G'day

Use =SUMIF()

Add another Column (F) Call it Currency.

eg

In the Cell you want to display the total place the following

=SUMIF(F:F,"Euro",E:E)

HTH
Mark.

 

[Sandy Mann]

With my default currency settings set to £, when I record a Macro for
formatting for £ in A1 and $ in A2 I get:

Range("A1").Select
Selection.NumberFormat = "$#,##0.00"
Range("A2").Select
Selection.NumberFormat = "\$#,###.00"

Using this the UDF:

Function SumItP(rRange As Range)
Application.Volatile
For Each cell In rRange
If cell.NumberFormat = "$#,##0.00" Then
Tot = Tot + cell.Value
End If
Next cell

SumItP = Tot
End Function

Sums Pounds and:

Function SumItD(rRange As Range)
Application.Volatile
For Each cell In rRange
If cell.NumberFormat = "\$#,###.00" Then
Tot = Tot + cell.Value
End If
Next cell

SumItD = Tot
End Function

Sums Dollars

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk