can you provide a sampe of your data ?
ie can you differentiate between the currencies via some means (either by another cell or by the format of the cell itself etc...)
To make this straightforward you need another reference cell which tells you whether the cell it is referring to is £,$ or €. Eg this could be in the row above or below. Then a simple SUMIF would suffice.
As it stands, it will be unnecessarily complicated to achieve this (if it is possible at all with formulas alone - you may be able to do something with XLM macro functions, which introduces its own quirks).
it may just be me but when I copied your sample data to my own XL I noticed that only the GBP values & % values are stored as numbers (the $ values are stored as text) so it follows that
=SUM(A1:AC1)-F1-L1 would give answer where F1 & L1 = % columns.
If the above is incorrect could you possible post up a slightly larger sample that illustrates the variations.
Thanks to you both. lasw10, the values should all be formatted as numbers - although thanks for pointing this out as the data was imported from another source and I have now corrected the formatting. I think that I will end up using the formula that you have given (this is where I started out but I thought there might be a simpler method that I was missing!).
Now starts the working week - ho hum. By the way lasw10 i'm in Suffolk too...
Not sure as to how "right/wrong" this is from best practice perspective, Richard can probably explain, but I figure the following VBA Function would do what you want ?
Code:
Function Currency_Total(rng As Range, cur As String)
Dim cell As Range
Dim ans As Double
For Each cell In rng
If InStr(cell.NumberFormatLocal, cur) > 0 Then ans = ans + cell.Value
Next cell
Currency_Total = ans
End Function
So using your sample data (where all formatted accordingly) -- and assuming data in A2:AC2 you could use the following in AD1:
=Currency_Total(A2:AC2,"£")
Or if you wanted to specify the currency you wanted to total for all rows in one cell - say in cell A1 you could use
=Currency_Total(A2:AC2,$A$1)
Result should be £959.02
If you changed A1 (or "£") to "$" then the answer would generate to 1,571.00
Hope that helps ?
Where abouts are you in Suffolk then ? I'm between Ipswich and Colchester...
right click on the Excel Icon next to "File" menu and select "View Code"
from Insert menu select Module
copy the function into this sheet and save your file.
NOTE: before being able to use the function you will need to ensure macro's are enabled in your security settings...
Back in "normal" XL go to Tools -> Options -> Macros -> Security -- ensure it's Medium (or leave as Low if already set), if left to High the function will never be available.
Save and close the file, reopen and when prompted select "Enable Macros".
We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel
Which adblocker are you using?
Disable AdBlock
Follow these easy steps to disable AdBlock
1)Click on the icon in the browser’s toolbar. 2)Click on the icon in the browser’s toolbar. 2)Click on the "Pause on this site" option.
Go back
Disable AdBlock Plus
Follow these easy steps to disable AdBlock Plus
1)Click on the icon in the browser’s toolbar. 2)Click on the toggle to disable it for "mrexcel.com".
Go back
Disable uBlock Origin
Follow these easy steps to disable uBlock Origin
1)Click on the icon in the browser’s toolbar. 2)Click on the "Power" button. 3)Click on the "Refresh" button.
Go back
Disable uBlock
Follow these easy steps to disable uBlock
1)Click on the icon in the browser’s toolbar. 2)Click on the "Power" button. 3)Click on the "Refresh" button.