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.

 

[piggylumps]

Having just started again on the whole sheet I am now showing #VALUE! in the Total $ column, GBP and EURO are still working fine, but if I put more rows in at the bottom then I get #NAME? returned in the totals column for the new rows.

 

[jasonb75]

The #VALUE! error is confusing me, that suggests that the $ values are not in a valid format.

Try the regular excel sum formula in the format of =SUM(G4,G5,G6) on the range holding the $ values, does that give the correct result?

#NAME? would suggest a typing error in the formula, either a mis-spelling of SUMCURR, EURO instead of "EURO", or you have tried to use a named range that doesn't exist.

 

[piggylumps]

The =SUM(...) returns a value of 0. All the spelling is correct with the right punctuation marks in the right place

 

[jasonb75]

If that returns 0 then there is something wrong with the $ values not the formula, they are being interpreted as text not as numbers.

Try double clicking in one of the $ value cells, does the value change from $ to decimal?

Then press enter, does the sum now show a value?

 

[piggylumps]

value didn't change from a $ to a decimal and when I pressed enter it changed the =SUMCURR values to #NAME? in all the total cells along that row. Really don't know what's going on with this now

 

[jasonb75]

Is the SUMCURR code still in the code module? That sounds like it could have been deleted / not saved.

With regard to the values issue, that is an input error, whoever has entered the amounts has entered $ into the cell instead of entering the amount and formatting the cell to $.

Once corrected the formula will work.

 

[piggylumps]

This is what is in the module:

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

I think it's still exactly as you first wrote it. Just changing the formats from general to currency now. Sorry to be a pain with this

 

[jasonb75]

The code is still correct, but if you're getting #NAME? then excel is not finding it. Which module is the code located in?

 

[piggylumps]

Not entirely sure what you mean but I've pressed Alt-F11 and its showing Module1, hope that makes sense to you and I haven't just stated the obvious

 

[jasonb75]

Makes perfect sense, and it's the answer I wanted to hear, that means the code is in the right place

Is it possible something in your macro security has changed? That could be blocking the code.