currency Problem

  • Thread starter irresistible007
  • Start date
I

irresistible007

I am having two sorts of Values one in USD while another in Euro...
this creates prob while doing conditional summing... it checks th
conditions and sumup all values falling into whether Euro and US
values.... this is a huge prob 4 me since i dont know how to tell Exce
not to sum Euro into USD

I havent typed the formula yet but i inadvace know what will happe
.... here is the sample Data

A B
John $500
Silvester $600
John €300
Silvester €300

Now if i wanted to sum for John it would simply give me $800 where a
it should give me $500 for dollars and €300 for euro
 
P

Pete

Can't you use another column to specify whether Euros or dollars with
some simple code like E or D? How about one column for dollars, another
for Euros?

Hope this helps.

Pete
 
R

Ron Rosenfeld

I am having two sorts of Values one in USD while another in Euro....
this creates prob while doing conditional summing... it checks the
conditions and sumup all values falling into whether Euro and USD
values.... this is a huge prob 4 me since i dont know how to tell Excel
not to sum Euro into USD

I havent typed the formula yet but i inadvace know what will happen
... here is the sample Data

A B
John $500
Silvester $600
John ¤300
Silvester ¤300

Now if i wanted to sum for John it would simply give me $800 where as
it should give me $500 for dollars and ¤300 for euros
Click to expand...

Here's one way. It relies on whether or not there is a Euro symbol in the cell
format string. That symbol has an ASCII Hex value of 80.

rng is the range to Sum (e.g. B2:B100) and cannot be an entire column.

1. Download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr

2. For Dollars:
=SUMPRODUCT((REGEX.FIND(XLM.GET.CELL(7,rng),"\x80")=0)*rng)

3. For Euros:
=SUMPRODUCT((REGEX.FIND(XLM.GET.CELL(7,rng),"\x80")>0)*rng)


--ron
 
Top