Sum of different currecies

Copper Contributor

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.

 

Screenshot 2024-01-15 212333.pngScreenshot 2024-01-15 213544.png

4 Replies

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

Hi@Riny_van_Eekelen , Thanks a lot for your response. Do you mind showing me an example? It would of great help.

Thanks

@sarathv7994 

Here is an example using a pivot table.

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

image.png