I am working on a project where expenditure across multiple accounts and multiple different currencies are consolidated into a single currency cash flow statement that aggregates the expenditure into categories on a monthly basis.

The table will be updated with currency exchange rates and new expenditures on existing accounts on a monthly basis. New accounts will be added as well overtime.

All the account sheets will be placed between tabs >>> <<< and will continue to have the exact same layout.

I have managed to solve the problem by converting all expenditures into a common currency (£) in case of all accounts between tabs >>> <<< in column H as a first step using

SUM($D5:$E5)/INDEX(GBPEUR,MATCH(DATE(YEAR($B5),MONTH($B5),DAY($B5)-1),Date_Currency,1))

and then aggregating the expenditure into categories per month as a second step using the below for each of the accounts between tabs >>> <<<

SUMIFS('2 (£)'!$H$5:$H$1000,'2 (£)'!$G$5:$G$1000,$B7,'2 (£)'!$B$5:$B$1000,">"&EOMONTH(C$4,-1),'2 (£)'!$B$5:$B$1000,"<="&C$4)

This is an imperfect solution though because 1) it is not future proof (if another account is added then the code needs to be updated), 2) the formula is lengthy and unwieldy, 3) there will be a lot of accounts and expenditures added into the spreadsheet and it will balloon in size, so try to avoid unnecessary steps being added.

I wonder whether there is a more elegant solution to the problem perhaps using SUMIFS and SUMPRODUCT together in a single step?

The objective is

1. For all sheets (accounts) between tabs >>> <<<
2. The formula shall convert to a single currency (£) all expenditure using the appropriate currency exchange rate on the date of the expenditure
3. The formula shall automatically choose the appropriate exchange rate based on the currency highlighted in cell B2 in case of each of the sheets (accounts) between tabs >>> <<<
4. The formula shall sum all the converted expenditures per categories and per month in one single step on "Cashflow" tab in cells C7:J91.

The excel spreadsheet is available on this link. Currency exchange rate data is on the "Exchange Rate" tab Categories are on the "Cashflow" tab in cells C7:C91