Forum Discussion

sarathv7994's avatar
sarathv7994
Copper Contributor
Jan 15, 2024

Sum of different currecies

Hi,

I have a tabular data with two different types of currencies in one column & expense type in another column, I need the sum of these currencies separately on the basis of the expense type, that is, The sum of  Debit and Credit amounts of both currencies should be shown separately, Is it possible? Hoping to find an answer.

Thanks in advance.

 

4 Replies

  • sarathv7994 

    The attached workbook was saved macro-enabled because it contains an XLM command to examine number formats.  Instead of using Pivot Tables, I have chosen to use Excel 365 formulas.  The first uses the soon-to-be-released PIVOTBY and the second builds the output using SUMIFS.

     

    Things get harder if the 'Currency' column is missing and all one has is number formats showing the Indian Rupee and Malay currency.  The GET.CELL(7, ref) is hidden within my GETFORMATλ Lambda function.  PIVOTBY works much as before but SUMIFS no longer works because the currency is an array rather than a range reference (using a helper range might have been easier).  That formula uses MAP to map headers to give the terms of the table.

     

    p.s. I have assumed debits to be negative when aggregating, but that might not be what was intended.

    = PIVOTBY(
        TransferTbl[TRANSFER TYPE],
        MAP(TransferTbl[AMOUNT], GetFormatλ),
        IF(Table1[TRANSFER TYPE]="CREDIT", Table1[AMOUNT], -Table1[AMOUNT]), SUM,,,,0
      )

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor

    sarathv7994 Best way to tackle this is to have one column with the amount and another with the currency code. Then you can use SUMIF, SUMIFS or a pivot table to summarize the amounts by transfer type and currency.

Resources