Forum Discussion
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!Arc7etWKEFo2jAazsIsC9d5Ksrap?e=OrvUP6
.
=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.
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!=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/F15which is copied down to cell G19. The SUMPRODUCT formula refers to the additional column.
