Sum of '£' values in a row with '£', '$' and 'Euro' values

N

Novice999

New Member
Joined
Jun 27, 2008
Messages
9
Good afternoon,

I am trying to write a formula which can calculate the total value of cells with '£' values in a row with '£', '$' and 'Euro' values.

Can you help? (as you can see I am a complete novice at all this)

Many thanks,
Novice999
 
Sort by date Sort by votes
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...)
 
Upvote 0
Hi,

The cells are formatted to the correct currency and this would be a good representation of the data I am using:

<TABLE style="WIDTH: 1573pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=2095 border=0><COLGROUP><COL style="WIDTH: 42pt; mso-width-source: userset; mso-width-alt: 2048" width=56><COL style="WIDTH: 64pt; mso-width-source: userset; mso-width-alt: 3108" width=85><COL style="WIDTH: 69pt; mso-width-source: userset; mso-width-alt: 3364" width=92><COL style="WIDTH: 46pt; mso-width-source: userset; mso-width-alt: 2230" width=61><COL style="WIDTH: 47pt; mso-width-source: userset; mso-width-alt: 2304" width=63><COL style="WIDTH: 43pt; mso-width-source: userset; mso-width-alt: 2084" width=57><COL style="WIDTH: 54pt; mso-width-source: userset; mso-width-alt: 2633" width=72><COL style="WIDTH: 58pt; mso-width-source: userset; mso-width-alt: 2816" width=77><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2889" width=79><COL style="WIDTH: 42pt; mso-width-source: userset; mso-width-alt: 2048" width=56><COL style="WIDTH: 46pt; mso-width-source: userset; mso-width-alt: 2230" width=61><COL style="WIDTH: 44pt; mso-width-source: userset; mso-width-alt: 2121" width=58><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2852" width=78><COL style="WIDTH: 55pt; mso-width-source: userset; mso-width-alt: 2669" width=73><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2742" width=75><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2596" width=71><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2560" width=70><COL style="WIDTH: 63pt; mso-width-source: userset; mso-width-alt: 3072" width=84><COL style="WIDTH: 71pt; mso-width-source: userset; mso-width-alt: 3474" width=95><COL style="WIDTH: 54pt; mso-width-source: userset; mso-width-alt: 2633" width=72><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2596" width=71><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2742" width=75><COL style="WIDTH: 61pt; mso-width-source: userset; mso-width-alt: 2962" width=81><COL style="WIDTH: 44pt; mso-width-source: userset; mso-width-alt: 2121" width=58><COL style="WIDTH: 49pt; mso-width-source: userset; mso-width-alt: 2377" width=65><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2596" width=71><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2889" width=79><COL style="WIDTH: 64pt; mso-width-source: userset; mso-width-alt: 3108" width=85><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2742" width=75><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl73 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 42pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: #666699" width=56 height=17>£577.00</TD><TD class=xl78 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 64pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #666699" width=85>N/A</TD><TD class=xl83 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 69pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #666699" width=92>YES</TD><TD class=xl78 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 46pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #666699" width=61>N/A</TD><TD class=xl78 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 47pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #666699" width=63>N/A</TD><TD class=xl74 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 43pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #666699" width=57>5.00%</TD><TD class=xl73 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 54pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #666699" width=72>£28.85</TD><TD class=xl79 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 58pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #666699" width=77>$1,200.00 </TD><TD class=xl83 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 59pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #666699" width=79>N/A</TD><TD class=xl78 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 42pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #666699" width=56>N/A</TD><TD class=xl80 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 46pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #666699" width=61>$365.00</TD><TD class=xl74 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 44pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #666699" width=58>16.18%</TD><TD class=xl80 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 59pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #666699" width=78>$194.16</TD><TD class=xl73 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 55pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #666699" width=73>£85.00</TD><TD class=xl78 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 56pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #666699" width=75>N/A</TD><TD class=xl78 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 53pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #666699" width=71>N/A</TD><TD class=xl73 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 53pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #666699" width=70>£25.00</TD><TD class=xl73 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 63pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #666699" width=84>£35.00</TD><TD class=xl73 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 71pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #666699" width=95>£105.00</TD><TD class=xl73 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 54pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #666699" width=72>£56.17</TD><TD class=xl75 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 53pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #666699" width=71>£15.00</TD><TD class=xl75 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 56pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #666699" width=75>£7.00</TD><TD class=xl76 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 61pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #666699" width=81>$6.00</TD><TD class=xl85 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 44pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #666699" width=58>N/A</TD><TD class=xl85 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 49pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #666699" width=65>N/A</TD><TD class=xl85 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 53pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #666699" width=71>N/A</TD><TD class=xl75 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 59pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #666699" width=79>£25.00</TD><TD class=xl85 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 64pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #666699" width=85>N/A</TD><TD class=xl85 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 56pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #666699" width=75>N/A</TD></TR></TBODY></TABLE>

Thanks for your help!
 
Upvote 0
Hi

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).
 
Upvote 0
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,
 
Upvote 0
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...
 
Upvote 0
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...
 
Upvote 0
Thanks. I have never used VBA before and have been trying to work out how to add the code you have given me but to no avail...

I work in Felixstowe and live in Ipswich so must be pretty near to you...
 
Upvote 0
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".
 
Last edited:
Upvote 0
Just got back from lunch and followed your instructions and it works - absolutely brilliant!

Thanks for your help - this is going to save me alot of time in the future! Really appreciated.
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question Question
Euro's Football Tournament
Replies
5
Views
397
ebc9
E
V
  • Solved
sum with criteria coming from another table
Replies
5
Views
89
Peter_SSs
Peter_SSs
S
  • Question Question
Help With Calculations
Replies
1
Views
116
ststern45
S
L
  • Question Question
Sumifs table by header row and column criteria
Replies
8
Views
531
Fluff
Fluff
J
  • Question Question
Employee Timesheet Pivot Table
Replies
2
Views
336
alansidman
alansidman

Share this page

We've detected that you are using an adblocker.

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.
Go back
Back
Top