+ Reply to Thread
Results 1 to 8 of 8

Sum EURO and USD values separately.

  1. #1
    Forum Expert contaminated's Avatar
    Join Date
    05-07-2009
    Location
    Baku, Azerbaijan
    MS-Off Ver
    Excel 2013
    Posts
    1,430

    Sum EURO and USD values separately.

    Hi

    I really stucked, and I don't know how to do this.

    A friend of mine prepared worksheet. In column "A" up to 800 entries representing household appliances. In column "B" their prices in two currencies. To be exact in USD and EURO.
    So he formatted prices column. For example TV 1500 $, smoothing-iron 99 EURO. and so on. So I need to sum USD values in C1, and EURO values in D1


    I tried many text formulas and other formulas from MOREFUNC.XLL, but no result...


    Thanks in advance.
    Attached Files Attached Files
    Last edited by contaminated; 05-30-2009 at 12:07 PM.
    Люди, питающие благие намерения, как раз и становятся чудовищами.

    Regards, ?Born in USSR?
    Vusal M Dadashev

    Baku, Azerbaijan

  2. #2
    Forum Expert contaminated's Avatar
    Join Date
    05-07-2009
    Location
    Baku, Azerbaijan
    MS-Off Ver
    Excel 2013
    Posts
    1,430

    Re: Sum EURO and USD values separately.

    All problems are that he formatted numbers with currency style....

  3. #3
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Sum EURO and USD values separately.

    You could use a UDF:
    Please Login or Register  to view this content.
    Then in F3, =SUMPRODUCT( (LEFT(CellText($B$2:$B$42), 1) = "€") * $B$2:$B$42)
    Entia non sunt multiplicanda sine necessitate

  4. #4
    Forum Expert contaminated's Avatar
    Join Date
    05-07-2009
    Location
    Baku, Azerbaijan
    MS-Off Ver
    Excel 2013
    Posts
    1,430

    Re: Sum EURO and USD values separately.

    Thanks for reply.
    I'll try it right away, but say me. Can it be done using ONLY excel built-in formulas. I guess no. But i need confirmation?!

  5. #5
    Forum Expert contaminated's Avatar
    Join Date
    05-07-2009
    Location
    Baku, Azerbaijan
    MS-Off Ver
    Excel 2013
    Posts
    1,430

    Re: Sum EURO and USD values separately.

    Great So;ution It works like a charm. Thanks...

  6. #6
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Sum EURO and USD values separately.

    Great. Would you please mark the thread as Solved?

  7. #7
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Sum EURO and USD values separately.

    First, install this little function to help out:
    Please Login or Register  to view this content.
    Then a helper column can be added (and hidden) to test for the "€" in the format string. In C2, enter this and copy down, then hide the row:

    =ISNUMBER(SEARCH("€",getformat(B2)))

    Then your two formulas would be:

    USD: =SUMIF($C$2:$C$42,FALSE,$B$2:$B$42)

    EURO: =SUMIF($C$2:$C$42,TRUE,$B$2:$B$42)
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  8. #8
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Sum EURO and USD values separately.

    Ouch...late to this party...

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1