Combinations

Functions get powerful when you combine them. Each page here takes one audit task, solves it with several functions in one formula, and takes the formula apart so you see what each piece does.

• Difficulty rating 2 of 5
  SUMIFSSUMDebtor age analysis table that ties to the control accountTotal the open amount per age band with one SUMIFS over a band table, add the bands up with SUM and check that the total agrees with the control account.=SUM(SUMIFS($C$2:$C$9, $B$2:$B$9, ">="&$F$8-$G$2:$G$6, $B$2:$B$9, "<="&$F$8-$F$2:$F$6))=SUM(SUMIFS($C$2:$C$9; $B$2:$B$9; ">="&$F$8-$G$2:$G$6; $B$2:$B$9; "<="&$F$8-$F$2:$F$6))
• Difficulty rating 3 of 5
  TEXTJOINFILTERAll invoices of one supplier in one cell with TEXTJOIN and FILTERPick the invoice numbers of one supplier with FILTER and join them into a single line with TEXTJOIN, with a message when the supplier has no invoices.Excel 2021 or later (This combination needs Excel 2021 or later)=TEXTJOIN(", ", TRUE, FILTER(B2:B9, A2:A9=H1, "No invoices"))=TEXTJOIN(", "; TRUE; FILTER(B2:B9; A2:A9=H1; "No invoices"))
• Difficulty rating 3 of 5
  TEXTAFTERTEXTBEFOREXLOOKUPFind the customer from a bank descriptionCut the invoice number out of a bank statement description with TEXTAFTER and TEXTBEFORE, then look up the customer in the receivables list with XLOOKUP.Excel 2024 or later (This combination needs Excel 2024 or later)=XLOOKUP(TEXTBEFORE(TEXTAFTER(H1, "REF:"), " ", 1, 0, 1), A2:A7, B2:B7, "Not in receivables", 0)=XLOOKUP(TEXTBEFORE(TEXTAFTER(H1; "REF:"); " "; 1; 0; 1); A2:A7; B2:B7; "Not in receivables"; 0)
• Difficulty rating 3 of 5
  FILTERISNAXMATCHInvoices missing from the ledger with FILTER, ISNA and XMATCHList the invoices on the receivables specification that are not in the general ledger: XMATCH looks each one up, ISNA turns every miss into TRUE, and FILTER keeps those rows, with a message when nothing is missing.Excel 2021 or later (This combination needs Excel 2021 or later)=FILTER(A2:C8, ISNA(XMATCH(A2:A8, D2:D8)), "Nothing missing")=FILTER(A2:C8; ISNA(XMATCH(A2:A8; D2:D8)); "Nothing missing")
• Difficulty rating 3 of 5
  TEXTSPLITTRIMTOCOLSplit a pasted list into a clean columnSplit a list of invoice numbers pasted into one cell on the commas, strip the stray spaces with TRIM and stack the pieces into one column with TOCOL, ready for matching.Excel 2024 or later (This combination needs Excel 2024 or later)=TOCOL(TRIM(TEXTSPLIT(A2, ",")))=TOCOL(TRIM(TEXTSPLIT(A2; ",")))
• Difficulty rating 3 of 5
  TAKESORTFILTERThe five largest manual entries with TAKE, SORT and FILTERList the five largest manual (MEM) journal lines for journal entry testing: FILTER keeps the MEM lines, SORT puts the largest amount first and TAKE keeps the first five rows.Excel 2024 or later (This combination needs Excel 2024 or later)=TAKE(SORT(FILTER(A2:E9, A2:A9="MEM"), 5, -1), 5)=TAKE(SORT(FILTER(A2:E9; A2:A9="MEM"); 5; -1); 5)
• Difficulty rating 3 of 5
  FILTERSUMYear-end cut-off test with FILTER and SUMFlag the sales invoices whose booking date and delivery date fall on different sides of year end and total them: two comparisons with the year-end date give TRUE or FALSE per date, FILTER keeps the invoices where the two differ, and SUM adds them up.Excel 2021 or later (This combination needs Excel 2021 or later)=SUM(FILTER(E2:E8, (C2:C8<=H1)<>(D2:D8<=H1), 0))=SUM(FILTER(E2:E8; (C2:C8<=H1)<>(D2:D8<=H1); 0))
• Difficulty rating 4 of 5
  LETFILTERHSTACKABSDifferences above the reporting threshold with LET, FILTER, HSTACK and ABSReconcile the ledger to the client's specification per customer and list only the differences above the reporting threshold: LET names the differences once, HSTACK pairs them with the customer, FILTER keeps the rows where ABS exceeds the threshold.Excel 2024 or later (This combination needs Excel 2024 or later)=LET(diff, B2:B7-C2:C7, FILTER(HSTACK(A2:A7, diff), ABS(diff)>F1, "No differences above threshold"))=LET(diff; B2:B7-C2:C7; FILTER(HSTACK(A2:A7; diff); ABS(diff)>F1; "No differences above threshold"))
• Difficulty rating 4 of 5
  VSTACKUNIQUEFILTERXMATCHISNADuplicate invoice numbers across two exportsStack two monthly purchase exports into one list and keep each invoice number that occurs more than once, using VSTACK, UNIQUE, XMATCH, ISNA and FILTER.Excel 2024 or later (This combination needs Excel 2024 or later)=FILTER(VSTACK(A2:A7, B2:B7), ISNA(XMATCH(VSTACK(A2:A7, B2:B7), UNIQUE(VSTACK(A2:A7, B2:B7), , TRUE))), "No duplicates")=FILTER(VSTACK(A2:A7; B2:B7); ISNA(XMATCH(VSTACK(A2:A7; B2:B7); UNIQUE(VSTACK(A2:A7; B2:B7); ; TRUE))); "No duplicates")
• Difficulty rating 4 of 5
  FILTERWEEKDAYCOUNTIFEntries posted on weekends and public holidays with FILTER, WEEKDAY and COUNTIFList the journal entries dated on a Saturday, a Sunday or a Dutch public holiday: WEEKDAY flags the weekends, COUNTIF checks the date against a holiday list, and FILTER keeps the flagged rows.Excel 2021 or later (This combination needs Excel 2021 or later)=FILTER(A2:D9, (WEEKDAY(B2:B9, 2)>5)+(COUNTIF(E2:E8, B2:B9)>0)>0, "No weekend or holiday entries")=FILTER(A2:D9; (WEEKDAY(B2:B9; 2)>5)+(COUNTIF(E2:E8; B2:B9)>0)>0; "No weekend or holiday entries")
• Difficulty rating 4 of 5
  XLOOKUPREGEXEXTRACTREGEXTESTFind the customer with a regex lookupPull the invoice number out of a bank description with REGEXEXTRACT, turn it into a pattern, and let XLOOKUP match that pattern against an invoice list that is typed in different ways.Excel for Microsoft 365 (This combination needs Excel for Microsoft 365)=IF(REGEXTEST(F1, "(?i)inv[- ]?\d{2}-?\d{4}"), LET(key, REGEXEXTRACT(F1, "(?i)inv[- ]?(\d{2}-?\d{4})", 2), XLOOKUP("(?i)^inv[- ]?"&LEFT(key, 2)&"-?"&RIGHT(key, 4)&"$", A2:A7, B2:B7, "Not in receivables", 3)), "No invoice number in text")=IF(REGEXTEST(F1; "(?i)inv[- ]?\d{2}-?\d{4}"); LET(key; REGEXEXTRACT(F1; "(?i)inv[- ]?(\d{2}-?\d{4})"; 2); XLOOKUP("(?i)^inv[- ]?"&LEFT(key; 2)&"-?"&RIGHT(key; 4)&"$"; A2:A7; B2:B7; "Not in receivables"; 3)); "No invoice number in text")
• Difficulty rating 4 of 5
  FILTERSEQUENCEXMATCHISNAGap test on invoice numbers with FILTER, SEQUENCE and XMATCHFind the missing numbers in a series of sales invoices: SEQUENCE builds every number from the lowest to the highest, XMATCH looks each one up in the ledger, ISNA marks the misses and FILTER lists them, with a message when there are no gaps.Excel 2021 or later (This combination needs Excel 2021 or later)=FILTER(SEQUENCE(MAX(A2:A9)-MIN(A2:A9)+1, 1, MIN(A2:A9)), ISNA(XMATCH(SEQUENCE(MAX(A2:A9)-MIN(A2:A9)+1, 1, MIN(A2:A9)), A2:A9)), "No gaps")=FILTER(SEQUENCE(MAX(A2:A9)-MIN(A2:A9)+1; 1; MIN(A2:A9)); ISNA(XMATCH(SEQUENCE(MAX(A2:A9)-MIN(A2:A9)+1; 1; MIN(A2:A9)); A2:A9)); "No gaps")
• Difficulty rating 4 of 5
  LETUNIQUESUMIFSFILTERHSTACKJournal entries where debit does not equal creditList 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.Excel 2024 or later (This combination needs Excel 2024 or later)=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"))
• Difficulty rating 4 of 5
  HSTACKUNIQUESUMIFSSORTMini trial balance from journal lines with HSTACK, UNIQUE and SUMIFSBuild 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.Excel 2024 or later (This combination needs Excel 2024 or later)=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))))
• Difficulty rating 4 of 5
  Under reviewTAKESORTBYRANDARRAYROWSRandom sample of journal lines with TAKE, SORTBY and RANDARRAYSelect n journal lines at random, never the same line twice: RANDARRAY gives every line a random key, SORTBY puts the lines in that order and TAKE keeps the first n.Excel 2024 or later (This combination needs Excel 2024 or later)=TAKE(SORTBY(A2:A13, RANDARRAY(ROWS(A2:A13))), G1)=TAKE(SORTBY(A2:A13; RANDARRAY(ROWS(A2:A13))); G1)
• Difficulty rating 4 of 5
  XLOOKUPSUMReceivables aging total per 30-day bandTurn every due date into a band with XLOOKUP and match_mode -1 on a band table, then let SUM add up the open amounts of one band.Excel 2021 or later (This combination needs Excel 2021 or later)=SUM((XLOOKUP($H$3-B2:B8, E2:E5, E2:E5, 0, -1)=H1)*C2:C8)=SUM((XLOOKUP($H$3-B2:B8; E2:E5; E2:E5; 0; -1)=H1)*C2:C8)
• Difficulty rating 4 of 5
  SUMIFSEDATESEQUENCERevenue per month for twelve months in one formulaTotal revenue for each of twelve months with one SUMIFS: SEQUENCE counts the months, EDATE turns each count into the first day of a month, and those dates become the criteria.Excel 2021 or later (This combination needs Excel 2021 or later)=SUMIFS(C2:C9, A2:A9, ">="&EDATE(F1, SEQUENCE(12, 1, 0)), A2:A9, "<"&EDATE(F1, SEQUENCE(12, 1, 1)))=SUMIFS(C2:C9; A2:A9; ">="&EDATE(F1; SEQUENCE(12; 1; 0)); A2:A9; "<"&EDATE(F1; SEQUENCE(12; 1; 1)))
• Difficulty rating 4 of 5
  FILTERMODROUNDABSRound-sum journal entries with FILTER, MOD, ROUND and ABSFlag ledger lines whose amount is an exact multiple of 1,000 (and above a minimum): ABS removes the sign, ROUND clears floating-point noise, MOD tests the multiple and FILTER lists the lines.Excel 2021 or later (This combination needs Excel 2021 or later)=FILTER(A2:E9, (MOD(ROUND(ABS(E2:E9), 2), G1)=0)*(ABS(E2:E9)>=G2), "No round-sum lines")=FILTER(A2:E9; (MOD(ROUND(ABS(E2:E9); 2); G1)=0)*(ABS(E2:E9)>=G2); "No round-sum lines")
• Difficulty rating 4 of 5
  INDEXSEQUENCEROUNDDOWNROWSSystematic sample of invoices with a fixed startSelect every k-th invoice from a population: the interval comes from ROUNDDOWN and ROWS, SEQUENCE counts the picks from a fixed start typed in a cell, and INDEX lists the selected rows.Excel 2021 or later (This combination needs Excel 2021 or later)=INDEX(A2:A25, G2+(SEQUENCE(G1)-1)*ROUNDDOWN(ROWS(A2:A25)/G1, 0))=INDEX(A2:A25; G2+(SEQUENCE(G1)-1)*ROUNDDOWN(ROWS(A2:A25)/G1; 0))
• Difficulty rating 4 of 5
  XLOOKUPTOCOLTwo-way lookup with one XLOOKUP and TOCOLFind the value where a row label and a column header meet, by turning both conditions into one grid of 0s and 1s and flattening it with TOCOL.Excel 2024 or later (This combination needs Excel 2024 or later)=XLOOKUP(1, TOCOL((H2=B1:D1)*(H1=A2:A6)), TOCOL(B2:D6), "Not found", 0)=XLOOKUP(1; TOCOL((H2=B1:D1)*(H1=A2:A6)); TOCOL(B2:D6); "Not found"; 0)