conditional sum question

[Devlin]

Hello,

I want to add a range of values, but they are formatted differently:
some of them are formatted with the dollar currency, others with the
euro currency. Excel treats them equally, as numbers, but I need to
make different sums automatically - I want to add only dollars or only
euros. Is there a way to use SUMIF? Is there a way to do this?

My data look like this:

$ 4,000
$ 2,230
€ 23,330
$ 12,212
€ 2,210
€ 900
$ 1,125
etc...

THANKS A LOT,
Devlin

 

[John Michl]

Unless you use some VB code to determine the type of currency based on
the format, I think you'll need to add a second column that indicates
the type. Then you'll be able to use SumIF.

You are correct in pointing out that Excel thinks of these values as
numbers not currency values. Same with other numerical formats such as
dates and times. The format only dresses up the look. Underneath is a
number like most any other number.

- John

 

[Devlin]

I will gladly add another column, but what formula should I use? I
can't input the Euro or Dollar signs in each cell of the column
manually, so I have to find a formula to do this for me... and after
that use a sumif

 

[Guest]

Paste the code below into a VBA module in your workbook, then use it to
identify the format type in cell A1 by entering this formula into cell B1

=CurrencyType(A1)

for guidance on how to use code, look at this site

http://www.mvps.org/dmcritchie/excel/getstarted.htm


Public Function CurrencyType(rng As Range) As String
If InStr(1, rng(1, 1).NumberFormat, "€") > 0 Then
CurrencyType = "Euro"
Exit Function
ElseIf InStr(1, rng(1, 1).NumberFormat, "$") > 0 Then
CurrencyType = "Dollar"
Exit Function
End If
CurrencyType = "None"
End Function

 

[Guest]

The function is lacking a line of code and, as a result, doesn't update
automatically.
It looks like that code ought to be as shown below. However, changing the
format of a cell doesn't appear to trigger a recalc of the sheet and,
derivatively, this function. So, after you change a cell's format, press the
F9 key to make these things all recalculate

Public Function CurrencyType(rng As Range) As String
Application.Volatile
If InStr(1, rng(1, 1).NumberFormat, "€") > 0 Then
CurrencyType = "Euro"
Exit Function
ElseIf InStr(1, rng(1, 1).NumberFormat, "$") > 0 Then
CurrencyType = "Dollar"
Exit Function
End If
CurrencyType = "None"
End Function

 

[RagDyer]

Since you're willing to use a "helper" column, you could try this:

Values in A1:A20,

Enter this in B1 and copy down to B20:

=LEFT(CELL("format",A1))

Then use this to total:

=SUMIF(B1:B20,"C",A1:A20)

--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

 

[Devlin]

Thank you all very much. The information was very helpful.

Devlin