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

Copper Contributor

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

5 Replies

# Re: Formula to Sum by Currency and then covert by each FX Rate

``=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.

# Re: Formula to Sum by Currency and then covert by each FX Rate

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!

# Re: Formula to Sum by Currency and then covert by each FX Rate

``=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.

# Re: Formula to Sum by Currency and then covert by each FX Rate

I work with Office365, is it a problem?

# Re: Formula to Sum by Currency and then covert by each FX Rate

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.