Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: Sum & Convert Currency

  1. #1
    Board Regular Caleeco's Avatar
    Join Date
    Jan 2016
    Location
    United Kingdom
    Posts
    758
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Sum & Convert Currency

    Hello,

    I am trying to set up an excel formula that will SUM currencies. However, they are listed without the currency format, so I need to convert EUR to GBP.

    I can achieve this using 2 SUMIF functions. However, from what i've read a much slicker formula could be achieved using LOOKUP and SUMPRODUCT.

    Formula in E3:
    Code:
    =SUMIF(G6:G23,"GBP",H6:H23)+(SUMIF(G6:G23,"EUR",H6:H23))/K6
    Formula in F3:
    Code:
    =SUMPRODUCT((LOOKUP(G6:G23,{"GBP","EUR"},{1,1.24})),H6:H23)
    Can anyone tell me why my formula in F3 doesn't resolve correctly?



    Many Thanks
    Caleeco
    Last edited by Caleeco; Jun 19th, 2017 at 01:45 PM.
    www.excelwtf.com/ - Helping you solve those WTF moments

  2. #2
    Board Regular
    Join Date
    Jun 2011
    Posts
    1,145
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sum & Convert Currency

    LOOKUP works on sorted array. Just reverse "GBP" AND "EUR" and the SUMPRODUCT formula will work.
    Excel 2010, Windows 7

  3. #3
    Board Regular FDibbins's Avatar
    Join Date
    Feb 2013
    Location
    Duncansville, PA USA
    Posts
    5,962
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sum & Convert Currency

    SUMIF is far more efficient than SUMPRODUCT, so while the SP may seem "slicker", it is probably slower than the SUMIF if used over large amounts of data

    - Posting guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    Regards

    Ford

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
This website uses cookies
We use cookies to store session information to facilitate remembering your login information, to allow you to save website preferences, to personalise content and ads, to provide social media features and to analyse our traffic. We also share information about your use of our site with our social media, advertising and analytics partners.
     


DMCA.com