Forum Discussion

bruosorio's avatar
bruosorio
Copper Contributor
Aug 20, 2022

Formula to Sum by Currency and then covert by each FX Rate

Hi guys, I would like to build a formula that summarize a table by each  currency and convert the total of each by a Fx Rate table then shows the results. Sample File: https://1drv.ms/x/s!Arc7etWKE...
Excel
Formulas and Functions
office 365
bruosorio's avatar
bruosorio
Copper Contributor

Hi OliverScheurich ,
That is a good approach however if the fx table has 10 or maybe 30 currencies listed the size of this formula will be huge to admin.
Thanks!

OliverScheurich's avatar
OliverScheurich
Gold Contributor
Aug 20, 2022

bruosorio 

=SUMPRODUCT(F$5:F$12*MMULT(N($E$5:$E$12=TRANSPOSE($E$15:$E$19)),$G$15:$G$19))

You can try this formula which is easier to adapt to any number of currencies. Enter the formula with ctrl+shift+enter if you don't work with Office365 or 2021. The formula works in the example where i added an additional column to the FX TABLE. In cell G15 is formula

=1/F15

which is copied down to cell G19. The SUMPRODUCT formula refers to the additional column.

  • bruosorio's avatar
    bruosorio
    Copper Contributor
    Aug 20, 2022
    I work with Office365, is it a problem?
    • OliverScheurich's avatar
      OliverScheurich
      Gold Contributor
      Aug 20, 2022

      bruosorio 

      No. Office365 offers functions such as LAMBDA, SEQUENCE, UNIQUE and FILTER which aren't available in Excel 2013 or Excel 2016. And the functions from Excel 2013 or Excel 2016 are available in Office365 of course. I usually work with Excel 2013 and the suggested formula is from Excel 2013.

       
       

Resources