1. You need a sheet Transactions with a table for imported data (Columns e.g. Organization, TransactionDate, SumIn, SumOut, optionally a calculated column TansactionYear with formula "= YEAR([@TransactionDate]"). Best will be to define this as a Table (e.g. tTransactions);
2. You need a sheet Organizations, optionally with field in 1st row where user can enter year number. When table is used for several years, and there will be a cell for year number, define this cell as Name, e.g. nRepYear.
3. On sheet Organizations, you need a table with columns Organization, Saldo (column headers must be not higher that row 3). And again, the best will be to define this as Table (e.g. tOrganizations)
4. In Table tOrganizations, fill the column Organization with list of all organizations from Table tTransactions;
5. Into 1st data row of Table tOrganizations, into column Saldo enter the formula:
Code:
=SUMIFS(tTransactions[SumIn],tTransacions[Organization],[@Organization],tTransacions[TransactionYear],nRepYear)-SUMIFS(tTransactions[SumOut],tTransacions[Organization],[@Organization],tTransacions[TransactionYear],nRepYear)
In case the workbook is meant for single year only, you can omit all columns TransactionDate, TransactionYear, the named range and the cell for entering report year, and second comparision in both parts of Saldo formula.
Current formula assumes, that in tTransactions you have separate columns for incoming and outgoing transactions, and sums in those columns are positive. Otherwise you have to make corrections in formula.
In table tOrganizations, in column Saldo is displayed current saldo for every organization (for report year determined by nRepYear).