Combination
Mini trial balance from journal lines with HSTACK, UNIQUE and SUMIFS
Build a list of accounts with their net postings from raw journal lines: UNIQUE gives the accounts, SUMIFS totals each one, HSTACK puts both side by side and SORT orders them.
Functions used:HSTACKUNIQUESUMIFSSORTExcel 2024 or later (This combination needs Excel 2024 or later)
Worked example
Net postings per account from the journal lines
Scenario
For the audit of Veldhuis Installatie BV you have exported four journal entries from the XAF auditfile, one row per line, with debit as a positive and credit as a negative amount. You want a mini trial balance per account to compare with the client's saldibalans, without building a pivot table.
The data
| Row | A | B | C | D | E | F | G | H |
|---|---|---|---|---|---|---|---|---|
| 1 | Entry no. | Account | Description | Amount | ||||
| 2 | 25-0601 | 45004500 | Rent October | €3,200.00€ 3.200,00 | 11001100=SORT(HSTACK(UNIQUE(B2:B9), SUMIFS(D2:D9, B2:B9, UNIQUE(B2:B9))))=SORT(HSTACK(UNIQUE(B2:B9); SUMIFS(D2:D9; B2:B9; UNIQUE(B2:B9)))) | €1,850.00€ 1.850,00 | ||
| 3 | 25-0601 | 16001600 | Rent October | -€3,200.00€ -3.200,00 | 13001300 | -€8,400.00€ -8.400,00 | ||
| 4 | 25-0602 | 11001100 | Customer receipt | €8,400.00€ 8.400,00 | 16001600 | -€3,200.00€ -3.200,00 | ||
| 5 | 25-0602 | 13001300 | Customer receipt | -€8,400.00€ -8.400,00 | 40004000 | €5,100.00€ 5.100,00 | ||
| 6 | 25-0603 | 40004000 | Salary run | €5,100.00€ 5.100,00 | 45004500 | €4,650.00€ 4.650,00 | ||
| 7 | 25-0603 | 11001100 | Salary run | -€5,100.00€ -5.100,00 | ||||
| 8 | 25-0604 | 45004500 | Utilities | €1,450.00€ 1.450,00 | ||||
| 9 | 25-0604 | 11001100 | Utilities | -€1,450.00€ -1.450,00 | ||||
| 10 |
Swipe sideways to see all columns
Formula in F2
=SORT(HSTACK(UNIQUE(B2:B9),SUMIFS(D2:D9, B2:B9, UNIQUE(B2:B9))))
=SORT(HSTACK(UNIQUE(B2:B9);SUMIFS(D2:D9; B2:B9; UNIQUE(B2:B9))))
Result: 1100 | €1,850.00 / 1300 | -€8,400.00 / 1600 | -€3,200.00 / 4000 | €5,100.00 / 4500 | €4,650.001100 | € 1.850,00 / 1300 | € -8.400,00 / 1600 | € -3.200,00 / 4000 | € 5.100,00 / 4500 | € 4.650,00
How the pieces fit together
Watch out: If some accounts are stored as text ("1100") and others as numbers, UNIQUE lists the same account twice and SUMIFS counts both spellings under each. Convert the account column to one type first.
The formula taken apart, from the inside out. Each part can be typed into its own cell to see what it returns.
=UNIQUE(B2:B9)
=UNIQUE(B2:B9)
arrayThe values to deduplicate; with several columns, whole rows are compared.Account column, 8 cellsPress Escape to closeLists each account once, in order of first appearance: 4500, 1600, 1100, 1300, 4000. The eight journal lines hold five different accounts.
=SUMIFS(D2:D9,
B2:B9, UNIQUE(B2:B9)) =SUMIFS(D2:D9;
B2:B9; UNIQUE(B2:B9)) SUMIFS SOMMEN.ALSAdds the numbers that meet all of several conditions.Read more about SUMIFSPress Escape to closesum_rangeThe cells whose numbers are added when a row passes every test.Amount column, 8 cellsPress Escape to closecriteria_range1The range the first condition is tested on; same size as sum_range.Account column, 8 cellsPress Escape to closeUNIQUE UNIEKReturns the distinct values of a range.Used here as: criteria1The test: a value, a cell, or text such as ">=100" or "MEM-*".Read more about UNIQUEPress Escape to closearrayThe values to deduplicate; with several columns, whole rows are compared.Account column, 8 cellsPress Escape to closeBecause the criterion is a list of five accounts, SUMIFS returns five totals, one per account:
- 4500 gives 3,200 + 1,450 = 4,650
- 1600 gives -3,200
- 1100 gives 8,400 - 5,100 - 1,450 = 1,850
- 1300 gives -8,400
- 4000 gives 5,100
=HSTACK(UNIQUE(B2:B9),
SUMIFS(D2:D9, B2:B9, UNIQUE(B2:B9))) =HSTACK(UNIQUE(B2:B9);
SUMIFS(D2:D9; B2:B9; UNIQUE(B2:B9))) UNIQUE UNIEKReturns the distinct values of a range.Used here as: array1The first range or array; it ends up in the leftmost columns.Read more about UNIQUEPress Escape to closearrayThe values to deduplicate; with several columns, whole rows are compared.Account column, 8 cellsPress Escape to closeSUMIFS SOMMEN.ALSAdds the numbers that meet all of several conditions.Used here as: array2Another range or array to place to the right; add as many as you need.Read more about SUMIFSPress Escape to closesum_rangeThe cells whose numbers are added when a row passes every test.Amount column, 8 cellsPress Escape to closecriteria_range1The range the first condition is tested on; same size as sum_range.Account column, 8 cellsPress Escape to closeUNIQUE UNIEKReturns the distinct values of a range.Used here as: criteria1The test: a value, a cell, or text such as ">=100" or "MEM-*".Read more about UNIQUEPress Escape to closearrayThe values to deduplicate; with several columns, whole rows are compared.Account column, 8 cellsPress Escape to closePlaces the account column and the totals column side by side: a table of 5 rows and 2 columns, still in order of first appearance (4500 first, 4000 last).
=SORT(HSTACK(UNIQUE(B2:B9),
SUMIFS(D2:D9, B2:B9, UNIQUE(B2:B9)))) =SORT(HSTACK(UNIQUE(B2:B9);
SUMIFS(D2:D9; B2:B9; UNIQUE(B2:B9)))) HSTACK HOR.STAPELENPlaces ranges side by side.Used here as: arrayThe range or array to sort; the source itself stays as it is.Read more about HSTACKPress Escape to closeUNIQUE UNIEKReturns the distinct values of a range.Used here as: array1The first range or array; it ends up in the leftmost columns.Read more about UNIQUEPress Escape to closearrayThe values to deduplicate; with several columns, whole rows are compared.Account column, 8 cellsPress Escape to closeSUMIFS SOMMEN.ALSAdds the numbers that meet all of several conditions.Used here as: array2Another range or array to place to the right; add as many as you need.Read more about SUMIFSPress Escape to closesum_rangeThe cells whose numbers are added when a row passes every test.Amount column, 8 cellsPress Escape to closecriteria_range1The range the first condition is tested on; same size as sum_range.Account column, 8 cellsPress Escape to closeUNIQUE UNIEKReturns the distinct values of a range.Used here as: criteria1The test: a value, a cell, or text such as ">=100" or "MEM-*".Read more about UNIQUEPress Escape to closearrayThe values to deduplicate; with several columns, whole rows are compared.Account column, 8 cellsPress Escape to closeSORT orders the table on its first column, so the accounts run from 1100 to 4500 like a trial balance:
- 1100 = 1,850
- 1300 = -8,400
- 1600 = -3,200
- 4000 = 5,100
- 4500 = 4,650
The five totals add up to 0, as they must when every entry balances.
Step by step
- UNIQUE(B2:B9) reads the accounts 4500, 1600, 1100, 1300, 4000, 1100, 4500, 1100 and keeps each once: 4500, 1600, 1100, 1300, 4000.
SUMIFS runs once per account in that list:
- 4500: 3,2003.200 + 1,4501.450 = 4,6504.650
- 1600: -3,2003.200
- 1100: 8,4008.400 - 5,1005.100 - 1,4501.450 = 1,8501.850
- 1300: -8,4008.400
- 4000: 5,1005.100
- HSTACK puts the accounts next to the totals: 4500 | 4,6504.650, 1600 | -3,2003.200, 1100 | 1,8501.850, 1300 | -8,4008.400, 4000 | 5,1005.100.
- SORT orders the rows on the account number, ascending: 1100, 1300, 1600, 4000, 4500, each with its total.
- The result spills from
F2overF2:G6. Check: 1,8501.850 - 8,4008.400 - 3,2003.200 + 5,1005.100 + 4,6504.650 = 0, so the journal lines balance.
Why it works
UNIQUE gives the list of accounts once, and the same list is used as the criterion of SUMIFS. A criterion that is a list makes SUMIFS return one total per item, in the same order, so the accounts and the totals line up row by row.
HSTACK joins them into a table and SORT puts it in trial balance order. When the journal changes, the table grows or shrinks by itself.
Watch out for
- The spill range
F2:G6must be empty. Anything in those cells gives a #SPILL! error. - The formula only covers the lines in
B2:D9. When the export gets longer, extend the ranges or use a table, otherwise lines are missing from your totals.
Variations
=SUMIFS(D2:D9,B2:B9, 1100)
=SUMIFS(D2:D9;B2:B9; 1100)
One account at a time: 8,4008.400 - 5,1005.100 - 1,4501.450 = 1,8501.850. Fine for a single account, but you type every account yourself and can miss one.
=HSTACK(SORT(UNIQUE(B2:B9)),SUMIFS(D2:D9, B2:B9, SORT(UNIQUE(B2:B9))))
=HSTACK(SORT(UNIQUE(B2:B9));SUMIFS(D2:D9; B2:B9; SORT(UNIQUE(B2:B9))))
Sorts the accounts first and then totals them in that order. Same table, but the list of accounts is written twice and sorting only on the account is all it can do.
=SUMIF(B2:B9,4500, D2:D9)
=SUMIF(B2:B9;4500; D2:D9)
The older SUMIF for one account: 3,2003.200 + 1,4501.450 = 4,6504.650. It works in every Excel version, but gives one number per formula and no list of accounts.
Related combinations
Functions used
- HSTACKPlaces ranges or arrays next to each other, left to right, into one spilled block that updates when the sources change.
- UNIQUEReturns each value of a range once, in the order it first appears, as a list that spills and follows the source.
- SUMIFSAdds the numbers in one range for the rows where every condition you set on other ranges is met.
- SORTReturns a sorted copy of a range, by any one of its columns, ascending or descending, as a spilled block that re-sorts itself when the data changes.
Functions used
- HSTACKPlaces ranges or arrays next to each other, left to right, into one spilled block that updates when the sources change.
- UNIQUEReturns each value of a range once, in the order it first appears, as a list that spills and follows the source.
- SUMIFSAdds the numbers in one range for the rows where every condition you set on other ranges is met.
- SORTReturns a sorted copy of a range, by any one of its columns, ascending or descending, as a spilled block that re-sorts itself when the data changes.