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
OliverScheurich's avatar
OliverScheurich
Gold Contributor

bruosorio 

=SUMPRODUCT(($E$5:$E$10=$E$14)*F$5:F$10*$F$14+($E$5:$E$10=$E$15)*F$5:F$10/$F$15+($E$5:$E$10=$E$16)*F$5:F$10/$F$16)

Maybe with this formula in cell F20 and filled across range F20:G20.

 

bruosorio's avatar
bruosorio
Copper Contributor
Aug 20, 2022

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