Calculating Dollars not GBP in a row

[piggylumps]

Is there anyway I can calculate the sum of a certain currency in a row while ignoring others?

Say for example I have a range of a1:d1, a1 will be $25, b1 will be £36, c1 will be $75 and d1 will be £5.

So what I'm looking for will be a formula in e1 which tells it to sum the range only if the numbers are entered in $'s.

Hope that makes sense.

 

[jasonb75]

Do the Yen currency cells contain ¥ in the format? If so it should work, try it on a single cell not a range.

 

[piggylumps]

Yep, cell has been formatted to show the Yen symbol and still nothing.

 

[jasonb75]

I didn't have time to test yesterday but just tried your code and it works for me with yen.

I assume you're using japanese yen ¥, not chinese yuan ￥ (which it would seem doesn't work).

 

[piggylumps]

Just reformatted the cells to different currencies and back to Yen again and for some reason it's decided to work properly now. Guess everything is entitled to having an off day. Thanks again

 

[jasonb75]

Try this instead

Public Function SUMCURR(rng As Range, curr As String) As Currency
Application.Volatile
Select Case curr
Case "USD"
curr = "$"
Case "EURO"
curr = "€"
Case "GBP"
curr = "£"
Case "YEN"
curr = "¥"
End Select
For Each cell In rng
If InStr(cell.Text, curr) Then
SUMCURR = SUMCURR + cell.Value
End If
Next
End Function

 

[piggylumps]

In best Monty Burns voice, "Excellent", thanks for that