+ Reply to Thread
Results 1 to 13 of 13

How to convert the currency and then sum up into single currency

  1. #1
    Registered User
    Join Date
    12-29-2015
    Location
    Malaysia
    MS-Off Ver
    Window 7
    Posts
    6

    How to convert the currency and then sum up into single currency

    Hi, I have a set of data in different currencies, how to I convert them into single currency then sum up into a single currency?
    I would love to have the figure in xx field by month.
    If I do it manually, the xx highlighted in red should be =(C2*I1)+(C5*I2)+(C8*I3), but I would like to have a single formula for all as there would be more names adding in and i would have to add it into the formula manually.
    I have tried Sumproduct, Sumif.. but I am stuck. Please help.

    CNY 0.1500
    HKD 0.1200
    IDR 0.0007

    Name Cur Jan Feb Mar
    Peter CNY 100 20 30
    Jane CNY 50 30 20
    Jack CNY 100 40 30
    Peter HKD 50
    Jane HKD 60 50
    Jack HKD
    Peter IDR 50 20 30
    Jane IDR 20 20
    Jack IDR 30 10

    Jan Feb Mar
    Peter USD xx xx xx
    Jane USD xx xx xx
    Jack USD xx xx xx

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: How to convert the currency and then sum up into single currency

    Any chance you could upload a sample workbook?

    Click on GO ADVANCED, scroll down and click Manage Attachments.
    If posting code please use code tags, see here.

  3. #3
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: How to convert the currency and then sum up into single currency

    One way:

    =SUMPRODUCT(($A$9:$A$17=$A21)*VLOOKUP($B$9:$B$17,$A$3:$B$5,2,FALSE)*C$9:C$17)

    copied across and down. See my sheet for layout (in absence of your sample sheet)
    Attached Files Attached Files
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  4. #4
    Registered User
    Join Date
    12-29-2015
    Location
    Malaysia
    MS-Off Ver
    Window 7
    Posts
    6

    Re: How to convert the currency and then sum up into single currency

    File attached.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    12-29-2015
    Location
    Malaysia
    MS-Off Ver
    Window 7
    Posts
    6

    Re: How to convert the currency and then sum up into single currency

    Quote Originally Posted by Glenn Kennedy View Post
    One way:

    =SUMPRODUCT(($A$9:$A$17=$A21)*VLOOKUP($B$9:$B$17,$A$3:$B$5,2,FALSE)*C$9:C$17)

    copied across and down. See my sheet for layout (in absence of your sample sheet)
    I have tried with the formula, but it doesn't seem to work Why?
    Attached Files Attached Files

  6. #6
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: How to convert the currency and then sum up into single currency

    Change the date-like text in the dark grey cells to real dates and format them as desired. then use this:

    =SUMPRODUCT(--(($A$3:$A$36=$A38)*IFERROR(VLOOKUP($B$3:$B$36,'Currency Rate'!$A$2:$B$8,2,FALSE),0))*D$3:D$36)

    Not etensively checked!!
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    12-29-2015
    Location
    Malaysia
    MS-Off Ver
    Window 7
    Posts
    6

    Re: How to convert the currency and then sum up into single currency

    Quote Originally Posted by Glenn Kennedy View Post
    Change the date-like text in the dark grey cells to real dates and format them as desired. then use this:

    =SUMPRODUCT(--(($A$3:$A$36=$A38)*IFERROR(VLOOKUP($B$3:$B$36,'Currency Rate'!$A$2:$B$8,2,FALSE),0))*D$3:D$36)

    Not etensively checked!!
    Hi Glenn,

    Thank you very much for the quick reply.
    I tried to do a manual calculation for Dec'20 column, but it seems the figure isn't right.
    Not sure why..
    Attached Files Attached Files

  8. #8
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: How to convert the currency and then sum up into single currency

    This is what I see...
    Attached Images Attached Images

  9. #9
    Registered User
    Join Date
    12-29-2015
    Location
    Malaysia
    MS-Off Ver
    Window 7
    Posts
    6

    Re: How to convert the currency and then sum up into single currency

    Quote Originally Posted by Glenn Kennedy View Post
    This is what I see...
    Attachment 716778

    OH my.. this is what I see, what is causing the difference?

  10. #10
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: How to convert the currency and then sum up into single currency

    There was a mistake in my file. The total was being calculated incorrectly. This is what I see. What do you see now?
    Attached Images Attached Images
    Attached Files Attached Files

  11. #11
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: How to convert the currency and then sum up into single currency

    ARRAY formula in D38 and copied across.

    =SUM(IFERROR(($A$3:$A$36=$A38)*($C$3:$C$36)*(D$3:D$36),0))

    To enter ARRAY formula
    Copy and paste the formula in cell
    Press F2
    Press Ctrl+Shift+Enter together
    Excel covers the formula with {}.
    Attached Files Attached Files
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  12. #12
    Registered User
    Join Date
    12-29-2015
    Location
    Malaysia
    MS-Off Ver
    Window 7
    Posts
    6

    Re: How to convert the currency and then sum up into single currency

    Thank you, both formula works, I will investigate and hope it will still works even if I edit or add more details.

  13. #13
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: How to convert the currency and then sum up into single currency

    Did you find out what (if anything) caused your issue with the totals previously described??

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Change currency in quotation (automatically if an certain currency is selected.
    By The excel apprentice in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 04-29-2021, 03:31 AM
  2. Replies: 2
    Last Post: 09-26-2018, 03:19 PM
  3. [SOLVED] Formula that contains currency not returning the results as currency
    By Triscia in forum Excel Formulas & Functions
    Replies: 17
    Last Post: 07-17-2014, 03:02 PM
  4. [SOLVED] Calculating amounts in a single currency from a list of multiple currency amounts
    By Romsky in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 07-03-2013, 12:22 PM
  5. Currency conversion insert currency symbol
    By siobeh in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 05-01-2013, 05:50 AM
  6. Replies: 5
    Last Post: 06-30-2011, 07:48 AM
  7. Currency Formatting-range of number as currency
    By kmurray24 in forum Excel General
    Replies: 1
    Last Post: 01-09-2008, 09:09 AM

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