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 ...
excel
PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Jan 16, 2024

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
  )

Resources