|
Sumif on Currency format
Hi,
I'm travelling for business throughout Europe. I've setup my expense claim in Excel in both Euro and GBP format on claims here and there. Would anybody know of a trick to do a SUMIF based on the currency format used, in order to get the total amounts in GBP and the total amount in Euro (each in it's own cell)? Thanks in advance for your help! Marc |
Sumif on Currency format
Let's say we have the following in column A:
ﾂ｣49.98 ぎ 29.16 ﾂ｣49.98 ぎ 29.16 ﾂ｣49.98 ぎ 29.16 First enter this small UDF: Function txet(r As Range) As String txet = r.Text End Function This function returns a real text string for its argument. In B1 enter: =txet(A1) and copy down. We se: ﾂ｣49.98 ﾂ｣49.98 ぎ 29.16 ぎ 29.16 ﾂ｣49.98 ﾂ｣49.98 ぎ 29.16 ぎ 29.16 ﾂ｣49.98 ﾂ｣49.98 ぎ 29.16 ぎ 29.16 Column B ;ooks link column A, but the currency symbols are "real"; therefore we can use sumproduct: =SUMPRODUCT((A1:A6),--(LEFT((B1:B6),1)="ﾂ｣")) which displays: 149.94 -- Gary''s Student - gsnu200742 "MarcD" wrote: Hi, I'm travelling for business throughout Europe. I've setup my expense claim in Excel in both Euro and GBP format on claims here and there. Would anybody know of a trick to do a SUMIF based on the currency format used, in order to get the total amounts in GBP and the total amount in Euro (each in it's own cell)? Thanks in advance for your help! Marc |
Sumif on Currency format
Hi Gary''s Student,
Thanks for your response. I'll see how I can work with this UDF (have not done it before). But I'll manage it. Best regards, Marc "Gary''s Student" wrote: Let's say we have the following in column A: ﾂ｣49.98 ぎ 29.16 ﾂ｣49.98 ぎ 29.16 ﾂ｣49.98 ぎ 29.16 First enter this small UDF: Function txet(r As Range) As String txet = r.Text End Function This function returns a real text string for its argument. In B1 enter: =txet(A1) and copy down. We se: ﾂ｣49.98 ﾂ｣49.98 ぎ 29.16 ぎ 29.16 ﾂ｣49.98 ﾂ｣49.98 ぎ 29.16 ぎ 29.16 ﾂ｣49.98 ﾂ｣49.98 ぎ 29.16 ぎ 29.16 Column B ;ooks link column A, but the currency symbols are "real"; therefore we can use sumproduct: =SUMPRODUCT((A1:A6),--(LEFT((B1:B6),1)="ﾂ｣")) which displays: 149.94 -- Gary''s Student - gsnu200742 "MarcD" wrote: Hi, I'm travelling for business throughout Europe. I've setup my expense claim in Excel in both Euro and GBP format on claims here and there. Would anybody know of a trick to do a SUMIF based on the currency format used, in order to get the total amounts in GBP and the total amount in Euro (each in it's own cell)? Thanks in advance for your help! Marc |
Sumif on Currency format
User Defined Functions (UDFs) are very easy to install and use: 1. ALT-F11 brings up the VBE window 2. ALT-I ALT-M opens a fresh module 3. paste the UDF in and close the VBE window If you save the workbook, the UDF will be saved with it. To remove the UDF: 1. bring up the VBE window as above 2. clear the code out 3. close the VBE window To learn more about UDFs and macros in general, see: http://www.mvps.org/dmcritchie/excel/getstarted.htm -- Gary''s Student - gsnu200743 "MarcD" wrote: Hi Gary''s Student, Thanks for your response. I'll see how I can work with this UDF (have not done it before). But I'll manage it. Best regards, Marc "Gary''s Student" wrote: Let's say we have the following in column A: ﾂ｣49.98 ぎ 29.16 ﾂ｣49.98 ぎ 29.16 ﾂ｣49.98 ぎ 29.16 First enter this small UDF: Function txet(r As Range) As String txet = r.Text End Function This function returns a real text string for its argument. In B1 enter: =txet(A1) and copy down. We se: ﾂ｣49.98 ﾂ｣49.98 ぎ 29.16 ぎ 29.16 ﾂ｣49.98 ﾂ｣49.98 ぎ 29.16 ぎ 29.16 ﾂ｣49.98 ﾂ｣49.98 ぎ 29.16 ぎ 29.16 Column B ;ooks link column A, but the currency symbols are "real"; therefore we can use sumproduct: =SUMPRODUCT((A1:A6),--(LEFT((B1:B6),1)="ﾂ｣")) which displays: 149.94 -- Gary''s Student - gsnu200742 "MarcD" wrote: Hi, I'm travelling for business throughout Europe. I've setup my expense claim in Excel in both Euro and GBP format on claims here and there. Would anybody know of a trick to do a SUMIF based on the currency format used, in order to get the total amounts in GBP and the total amount in Euro (each in it's own cell)? Thanks in advance for your help! Marc |
| All times are GMT +1. The time now is 06:09 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com