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

[bruosorio]

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!Arc7etWKEFo2jAazsIsC9d5Ksrap?e=OrvUP6

. 

[#2]

@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.

[#3]

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!

[#4]

@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.

[#5]

I work with Office365, is it a problem?

[#6]

@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.