Combination
Journal entries where debit does not equal credit
List the entries whose debit and credit totals differ: take the unique entry numbers, total debit and credit per entry with SUMIFS, and keep the unequal ones with FILTER over HSTACK, all named in one LET.
Functions used:LETUNIQUESUMIFSFILTERHSTACKExcel 2024 or later (This combination needs Excel 2024 or later)
Difficulty rating 4 of 5
Worked example
Entries that do not balance
Scenario
For Veldhuis Installatie BV you received the journal lines of the first quarter of 2025 from the XAF auditfile. Before you rely on the ledger you check that debit equals credit per entry. You want one formula that lists every entry that does not balance, with its debit, credit and difference.
The data
| Row | A | B | C | D | E | F | G | H | I | J | K | L |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Entry no. | Account | Debit | Credit | Entry no. | 25-0415 | ||||||
| 2 | 25-0412 | 13001300 | €12,100.00€ 12.100,00 | €0.00€ 0,00 | 25-0415=LET(e, UNIQUE(A2:A9), debit, SUMIFS(C2:C9, A2:A9, e), credit, SUMIFS(D2:D9, A2:A9, e), FILTER(HSTACK(e, debit, credit, debit-credit), debit<>credit, "All entries balance"))=LET(e; UNIQUE(A2:A9); debit; SUMIFS(C2:C9; A2:A9; e); credit; SUMIFS(D2:D9; A2:A9; e); FILTER(HSTACK(e; debit; credit; debit-credit); debit<>credit; "All entries balance")) | €3,500.00€ 3.500,00 | €3,050.00€ 3.050,00 | €450.00€ 450,00 | ||||
| 3 | 25-0412 | 80008000 | €0.00€ 0,00 | €10,000.00€ 10.000,00 | 25-0431 | €5,200.00€ 5.200,00 | €0.00€ 0,00 | €5,200.00€ 5.200,00 | ||||
| 4 | 25-0412 | 18001800 | €0.00€ 0,00 | €2,100.00€ 2.100,00 | ||||||||
| 5 | 25-0415 | 45004500 | €3,500.00€ 3.500,00 | €0.00€ 0,00 | ||||||||
| 6 | 25-0415 | 16001600 | €0.00€ 0,00 | €3,050.00€ 3.050,00 | ||||||||
| 7 | 25-0420 | 11001100 | €8,400.00€ 8.400,00 | €0.00€ 0,00 | ||||||||
| 8 | 25-0420 | 13001300 | €0.00€ 0,00 | €8,400.00€ 8.400,00 | ||||||||
| 9 | 25-0431 | 40004000 | €5,200.00€ 5.200,00 | €0.00€ 0,00 | ||||||||
| 10 |
Swipe sideways to see all columns
Formula in F2
=LET(e,UNIQUE(A2:A9), debit, SUMIFS(C2:C9, A2:A9, e), credit, SUMIFS(D2:D9, A2:A9, e), FILTER(HSTACK(e, debit, credit, debit-credit), debit<>credit, "All entries balance"))
=LET(e;UNIQUE(A2:A9); debit; SUMIFS(C2:C9; A2:A9; e); credit; SUMIFS(D2:D9; A2:A9; e); FILTER(HSTACK(e; debit; credit; debit-credit); debit<>credit; "All entries balance"))
LETNames intermediate values so a formula reads clearly and computes each once.Read more about LETPress Escape to close
name1The name you give the first value. Must start with a letter.Variable defined in this LETBound to:
UNIQUE(A2:A9)Press Escape to closeUNIQUE UNIEKReturns the distinct values of a range.Used here as: name_value1The value or expression that name1 stands for.Read more about UNIQUEPress Escape to close
arrayThe values to deduplicate; with several columns, whole rows are compared.Entry no. column, 8 cellsPress Escape to close
calculation_or_name2The final calculation, or the next name to define.Variable defined in this LETBound to:
SUMIFS(C2:C9, A2:A9, e)SUMIFS(C2:C9; A2:A9; e)Press Escape to closeSUMIFS SOMMEN.ALSAdds the numbers that meet all of several conditions.Used here as: name_value2The value bound to the previous name.Read more about SUMIFSPress Escape to close
sum_rangeThe cells whose numbers are added when a row passes every test.Debit column, 8 cellsPress Escape to close
criteria_range1The range the first condition is tested on; same size as sum_range.Entry no. column, 8 cellsPress Escape to close
criteria1The test: a value, a cell, or text such as ">=100" or "MEM-*".Variable defined in this LETBound to:
UNIQUE(A2:A9)Press Escape to closecalculation_or_name3 (optional)The final calculation, or another name; pairs may continue.Variable defined in this LETBound to:
SUMIFS(D2:D9, A2:A9, e)SUMIFS(D2:D9; A2:A9; e)Press Escape to closeSUMIFS SOMMEN.ALSAdds the numbers that meet all of several conditions.Used here as: name_value3The value bound to the previous name.Read more about SUMIFSPress Escape to close
sum_rangeThe cells whose numbers are added when a row passes every test.Credit column, 8 cellsPress Escape to close
criteria_range1The range the first condition is tested on; same size as sum_range.Entry no. column, 8 cellsPress Escape to close
criteria1The test: a value, a cell, or text such as ">=100" or "MEM-*".Variable defined in this LETBound to:
UNIQUE(A2:A9)Press Escape to closeFILTERReturns every row of a range that meets a condition.Used here as: calculation_or_name4The final calculation, or another name; pairs may continue.Read more about FILTERPress Escape to close
HSTACK HOR.STAPELENPlaces ranges side by side.Used here as: arrayThe rows you want back: one column or a whole table.Read more about HSTACKPress Escape to close
array1The first range or array; it ends up in the leftmost columns.Variable defined in this LETBound to:
UNIQUE(A2:A9)Press Escape to closearray2 (optional)Another range or array to place to the right; add as many as you need.Variable defined in this LETBound to:
SUMIFS(C2:C9, A2:A9, e)SUMIFS(C2:C9; A2:A9; e)Press Escape to closearray3 (optional)Another range or array to place to the right; add as many as you need.Variable defined in this LETBound to:
SUMIFS(D2:D9, A2:A9, e)SUMIFS(D2:D9; A2:A9; e)Press Escape to closearray4 (optional)Another range or array to place to the right; add as many as you need.Variable defined in this LETBound to:
SUMIFS(C2:C9, A2:A9, e)SUMIFS(C2:C9; A2:A9; e)Press Escape to close-SubtractsPress Escape to close
creditVariable defined in this LETBound to:
SUMIFS(D2:D9, A2:A9, e)SUMIFS(D2:D9; A2:A9; e)Press Escape to closeincludeOne TRUE/FALSE per row, as tall as array (as wide for columns): what to keep.Variable defined in this LETBound to:
SUMIFS(C2:C9, A2:A9, e)SUMIFS(C2:C9; A2:A9; e)Press Escape to close<>Not equal toPress Escape to close
creditVariable defined in this LETBound to:
SUMIFS(D2:D9, A2:A9, e)SUMIFS(D2:D9; A2:A9; e)Press Escape to closeif_empty (optional)What to show when no row passes; without it you get #CALC!.Press Escape to close
Result: 25-0415 | €3,500.00 | €3,050.00 | €450.00 / 25-0431 | €5,200.00 | €0.00 | €5,200.0025-0415 | € 3.500,00 | € 3.050,00 | € 450,00 / 25-0431 | € 5.200,00 | € 0,00 | € 5.200,00
How the pieces fit together
Watch out: Amounts with decimals can differ by a fraction of a cent through floating point arithmetic, so debit<>credit may flag an entry that balances in practice. Compare the rounded difference instead (see the variation with ROUND).
The formula taken apart, from the inside out. Each part can be typed into its own cell to see what it returns.
=UNIQUE(A2:A9)
=UNIQUE(A2:A9)
arrayThe values to deduplicate; with several columns, whole rows are compared.Entry no. column, 8 cellsPress Escape to closeThe entry numbers once each, in order of first appearance:
25-0412,25-0415,25-0420,25-0431. Eight journal lines become four entries.=SUMIFS(C2:C9,
A2:A9, UNIQUE(A2:A9)) =SUMIFS(C2:C9;
A2:A9; UNIQUE(A2:A9)) 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.Debit column, 8 cellsPress Escape to closecriteria_range1The range the first condition is tested on; same size as sum_range.Entry no. 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.Entry no. column, 8 cellsPress Escape to closeTotal debit per entry. Because the criteria argument is a column of four entry numbers, SUMIFS returns four totals: 12,100, 3,500, 8,400 and 5,200.
=SUMIFS(D2:D9,
A2:A9, UNIQUE(A2:A9)) =SUMIFS(D2:D9;
A2:A9; UNIQUE(A2:A9)) 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.Credit column, 8 cellsPress Escape to closecriteria_range1The range the first condition is tested on; same size as sum_range.Entry no. 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.Entry no. column, 8 cellsPress Escape to closeTotal credit per entry in the same order: 10,000 + 2,100 = 12,100, then 3,050, 8,400 and 0.
=SUMIFS(C2:C9,
A2:A9, UNIQUE(A2:A9)) - SUMIFS(D2:D9, A2:A9, UNIQUE(A2:A9)) =SUMIFS(C2:C9;
A2:A9; UNIQUE(A2:A9)) - SUMIFS(D2:D9; A2:A9; UNIQUE(A2:A9)) 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.Debit column, 8 cellsPress Escape to closecriteria_range1The range the first condition is tested on; same size as sum_range.Entry no. 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.Entry no. column, 8 cellsPress Escape to close-SubtractsPress Escape to closeSUMIFS 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.Credit column, 8 cellsPress Escape to closecriteria_range1The range the first condition is tested on; same size as sum_range.Entry no. 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.Entry no. column, 8 cellsPress Escape to closeDebit minus credit per entry: 0, 450, 0 and 5,200. Every value other than 0 is an entry that does not balance.
=LET(e,
UNIQUE(A2:A9), debit, SUMIFS(C2:C9, A2:A9, e), credit, SUMIFS(D2:D9, A2:A9, e), FILTER(HSTACK(e, debit, credit, debit-credit), debit<>credit, "All entries balance")) =LET(e;
UNIQUE(A2:A9); debit; SUMIFS(C2:C9; A2:A9; e); credit; SUMIFS(D2:D9; A2:A9; e); FILTER(HSTACK(e; debit; credit; debit-credit); debit<>credit; "All entries balance")) LETNames intermediate values so a formula reads clearly and computes each once.Read more about LETPress Escape to closename1The name you give the first value. Must start with a letter.Variable defined in this LETBound to:UNIQUE(A2:A9)Press Escape to closeUNIQUE UNIEKReturns the distinct values of a range.Used here as: name_value1The value or expression that name1 stands for.Read more about UNIQUEPress Escape to closearrayThe values to deduplicate; with several columns, whole rows are compared.Entry no. column, 8 cellsPress Escape to closecalculation_or_name2The final calculation, or the next name to define.Variable defined in this LETBound to:SUMIFS(C2:C9, A2:A9, e)SUMIFS(C2:C9; A2:A9; e)Press Escape to closeSUMIFS SOMMEN.ALSAdds the numbers that meet all of several conditions.Used here as: name_value2The value bound to the previous name.Read more about SUMIFSPress Escape to closesum_rangeThe cells whose numbers are added when a row passes every test.Debit column, 8 cellsPress Escape to closecriteria_range1The range the first condition is tested on; same size as sum_range.Entry no. column, 8 cellsPress Escape to closecriteria1The test: a value, a cell, or text such as ">=100" or "MEM-*".Variable defined in this LETBound to:UNIQUE(A2:A9)Press Escape to closecalculation_or_name3 (optional)The final calculation, or another name; pairs may continue.Variable defined in this LETBound to:SUMIFS(D2:D9, A2:A9, e)SUMIFS(D2:D9; A2:A9; e)Press Escape to closeSUMIFS SOMMEN.ALSAdds the numbers that meet all of several conditions.Used here as: name_value3The value bound to the previous name.Read more about SUMIFSPress Escape to closesum_rangeThe cells whose numbers are added when a row passes every test.Credit column, 8 cellsPress Escape to closecriteria_range1The range the first condition is tested on; same size as sum_range.Entry no. column, 8 cellsPress Escape to closecriteria1The test: a value, a cell, or text such as ">=100" or "MEM-*".Variable defined in this LETBound to:UNIQUE(A2:A9)Press Escape to closeFILTERReturns every row of a range that meets a condition.Used here as: calculation_or_name4The final calculation, or another name; pairs may continue.Read more about FILTERPress Escape to closeHSTACK HOR.STAPELENPlaces ranges side by side.Used here as: arrayThe rows you want back: one column or a whole table.Read more about HSTACKPress Escape to closearray1The first range or array; it ends up in the leftmost columns.Variable defined in this LETBound to:UNIQUE(A2:A9)Press Escape to closearray2 (optional)Another range or array to place to the right; add as many as you need.Variable defined in this LETBound to:SUMIFS(C2:C9, A2:A9, e)SUMIFS(C2:C9; A2:A9; e)Press Escape to closearray3 (optional)Another range or array to place to the right; add as many as you need.Variable defined in this LETBound to:SUMIFS(D2:D9, A2:A9, e)SUMIFS(D2:D9; A2:A9; e)Press Escape to closearray4 (optional)Another range or array to place to the right; add as many as you need.Variable defined in this LETBound to:SUMIFS(C2:C9, A2:A9, e)SUMIFS(C2:C9; A2:A9; e)Press Escape to close-SubtractsPress Escape to closecreditVariable defined in this LETBound to:SUMIFS(D2:D9, A2:A9, e)SUMIFS(D2:D9; A2:A9; e)Press Escape to closeincludeOne TRUE/FALSE per row, as tall as array (as wide for columns): what to keep.Variable defined in this LETBound to:SUMIFS(C2:C9, A2:A9, e)SUMIFS(C2:C9; A2:A9; e)Press Escape to close<>Not equal toPress Escape to closecreditVariable defined in this LETBound to:SUMIFS(D2:D9, A2:A9, e)SUMIFS(D2:D9; A2:A9; e)Press Escape to closeif_empty (optional)What to show when no row passes; without it you get #CALC!.Press Escape to closeLET names the entries (e), the debit totals (debit) and the credit totals (credit) once.
HSTACK puts entry, debit, credit and difference side by side in a 4 by 4 grid, and FILTER keeps the rows where debit<>credit: entries
25-0415and25-0431. If every entry balanced, the message would show instead.
Step by step
- e = UNIQUE(A2:A9) gives the four entries
25-0412,25-0415,25-0420and25-0431. debit = SUMIFS(C2:C9, A2:A9, e) totals the debit per entry:
25-0412: 12,10012.10025-0415: 3,5003.50025-0420: 8,4008.40025-0431: 5,2005.200
credit = SUMIFS(D2:D9, A2:A9, e) totals the credit per entry:
25-0412: 10,00010.000 + 2,1002.100 = 12,10012.10025-0415: 3,0503.05025-0420: 8,4008.40025-0431: 0 (that entry has no credit line)
- debit-credit gives the differences: 12,10012.100 - 12,10012.100 = 0, 3,5003.500 - 3,0503.050 = 450, 8,4008.400 - 8,4008.400 = 0 and 5,2005.200 - 0 = 5,2005.200.
- HSTACK(e, debit, credit, debit-credit) places the four columns side by side: a grid of 4 rows and 4 columns, one row per entry.
- debit<>credit is FALSE, TRUE, FALSE, TRUE, so FILTER keeps rows 2 and 4:
25-0415(3,5003.500 / 3,0503.050 / 450) and25-0431(5,2005.200 / 0 / 5,2005.200). The result fillsF2:I3.
Why it works
SUMIFS accepts a whole column of criteria and returns one total per criterion, so UNIQUE and SUMIFS together give one debit and one credit total per entry without helper columns.
LET names those three results once, so debit and credit are used twice (in the grid and in the test) without being calculated twice. FILTER can then test debit<>credit against the rows of the HSTACK grid, because both have one row per entry.
Watch out for
- An entry that is missing a line altogether, such as
25-0431here, shows a credit of 0 and is caught. But an entry with equal debit and credit on the wrong accounts balances and is not flagged: this test checks the totals, not the accounts. - If some lines carry a trailing space or inconsistent formatting in the entry number (for example
"25-0412 "next to"25-0412"), UNIQUE lists the entry twice, and SUMIFS counts only the lines that match each exact form, so each half looks unbalanced. Clean columnAwith TRIM first.
Variations
=LET(e,UNIQUE(A2:A9), debit, SUMIFS(C2:C9, A2:A9, e), credit, SUMIFS(D2:D9, A2:A9, e), FILTER(HSTACK(e, debit, credit, debit-credit), ROUND(debit-credit, 2)<>0, "All entries balance"))
=LET(e;UNIQUE(A2:A9); debit; SUMIFS(C2:C9; A2:A9; e); credit; SUMIFS(D2:D9; A2:A9; e); FILTER(HSTACK(e; debit; credit; debit-credit); ROUND(debit-credit; 2)<>0; "All entries balance"))
LETNames intermediate values so a formula reads clearly and computes each once.Read more about LETPress Escape to close
name1The name you give the first value. Must start with a letter.Variable defined in this LETBound to:
UNIQUE(A2:A9)Press Escape to closeUNIQUE UNIEKReturns the distinct values of a range.Used here as: name_value1The value or expression that name1 stands for.Read more about UNIQUEPress Escape to close
arrayThe values to deduplicate; with several columns, whole rows are compared.Entry no. column, 8 cellsPress Escape to close
calculation_or_name2The final calculation, or the next name to define.Variable defined in this LETBound to:
SUMIFS(C2:C9, A2:A9, e)SUMIFS(C2:C9; A2:A9; e)Press Escape to closeSUMIFS SOMMEN.ALSAdds the numbers that meet all of several conditions.Used here as: name_value2The value bound to the previous name.Read more about SUMIFSPress Escape to close
sum_rangeThe cells whose numbers are added when a row passes every test.Debit column, 8 cellsPress Escape to close
criteria_range1The range the first condition is tested on; same size as sum_range.Entry no. column, 8 cellsPress Escape to close
criteria1The test: a value, a cell, or text such as ">=100" or "MEM-*".Variable defined in this LETBound to:
UNIQUE(A2:A9)Press Escape to closecalculation_or_name3 (optional)The final calculation, or another name; pairs may continue.Variable defined in this LETBound to:
SUMIFS(D2:D9, A2:A9, e)SUMIFS(D2:D9; A2:A9; e)Press Escape to closeSUMIFS SOMMEN.ALSAdds the numbers that meet all of several conditions.Used here as: name_value3The value bound to the previous name.Read more about SUMIFSPress Escape to close
sum_rangeThe cells whose numbers are added when a row passes every test.Credit column, 8 cellsPress Escape to close
criteria_range1The range the first condition is tested on; same size as sum_range.Entry no. column, 8 cellsPress Escape to close
criteria1The test: a value, a cell, or text such as ">=100" or "MEM-*".Variable defined in this LETBound to:
UNIQUE(A2:A9)Press Escape to closeFILTERReturns every row of a range that meets a condition.Used here as: calculation_or_name4The final calculation, or another name; pairs may continue.Read more about FILTERPress Escape to close
HSTACK HOR.STAPELENPlaces ranges side by side.Used here as: arrayThe rows you want back: one column or a whole table.Read more about HSTACKPress Escape to close
array1The first range or array; it ends up in the leftmost columns.Variable defined in this LETBound to:
UNIQUE(A2:A9)Press Escape to closearray2 (optional)Another range or array to place to the right; add as many as you need.Variable defined in this LETBound to:
SUMIFS(C2:C9, A2:A9, e)SUMIFS(C2:C9; A2:A9; e)Press Escape to closearray3 (optional)Another range or array to place to the right; add as many as you need.Variable defined in this LETBound to:
SUMIFS(D2:D9, A2:A9, e)SUMIFS(D2:D9; A2:A9; e)Press Escape to closearray4 (optional)Another range or array to place to the right; add as many as you need.Variable defined in this LETBound to:
SUMIFS(C2:C9, A2:A9, e)SUMIFS(C2:C9; A2:A9; e)Press Escape to close-SubtractsPress Escape to close
creditVariable defined in this LETBound to:
SUMIFS(D2:D9, A2:A9, e)SUMIFS(D2:D9; A2:A9; e)Press Escape to closeROUND AFRONDENRounds to a number of decimal places.Used here as: includeOne TRUE/FALSE per row, as tall as array (as wide for columns): what to keep.Read more about ROUNDPress Escape to close
numberThe value to round; a number or a cell that holds one.Variable defined in this LETBound to:
SUMIFS(C2:C9, A2:A9, e)SUMIFS(C2:C9; A2:A9; e)Press Escape to close-SubtractsPress Escape to close
creditVariable defined in this LETBound to:
SUMIFS(D2:D9, A2:A9, e)SUMIFS(D2:D9; A2:A9; e)Press Escape to closenum_digitsDecimal places to keep: 2 for cents, 0 for whole numbers, -3 for thousands.2: for cents, 0 for whole numbers, -3 for thousandsPress Escape to close
<>Not equal toPress Escape to close
NumberLiteral valuePress Escape to close
if_empty (optional)What to show when no row passes; without it you get #CALC!.Press Escape to close
Tests the difference rounded to two decimals, so tiny floating point differences are ignored. Same two entries here.
=SUMIF(A2:A9,K1, C2:C9) - SUMIF(A2:A9, K1, D2:D9)
=SUMIF(A2:A9;K1; C2:C9) - SUMIF(A2:A9; K1; D2:D9)
rangeThe cells the condition is tested against.Entry no. column, 8 cellsPress Escape to close
criteriaThe condition: a value, a cell, or text such as ">100" or "MEM-*".Input cell: 25-0415Press Escape to close
sum_range (optional)The cells to add; left out means range itself.Debit column, 8 cellsPress Escape to close
-SubtractsPress Escape to close
rangeThe cells the condition is tested against.Entry no. column, 8 cellsPress Escape to close
criteriaThe condition: a value, a cell, or text such as ">100" or "MEM-*".Input cell: 25-0415Press Escape to close
sum_range (optional)The cells to add; left out means range itself.Credit column, 8 cellsPress Escape to close
The older way, one entry at a time: type an entry number in K1 and get its difference (450 for 25-0415). It works in every Excel version, but you must repeat it for each entry.
Related combinations
Functions used
- LETGives a value or an intermediate result a name inside one formula, so you can reuse it by name and Excel calculates it only once.
- 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.
- FILTERReturns every row of a range that meets your condition, as a live list that spills into the cells below and updates when the data changes.
- HSTACKPlaces ranges or arrays next to each other, left to right, into one spilled block that updates when the sources change.
Functions used
- LETGives a value or an intermediate result a name inside one formula, so you can reuse it by name and Excel calculates it only once.
- 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.
- FILTERReturns every row of a range that meets your condition, as a live list that spills into the cells below and updates when the data changes.
- HSTACKPlaces ranges or arrays next to each other, left to right, into one spilled block that updates when the sources change.