Self-auditing formula pattern, parts, and common failure modes
SUMIF + Balance Checks + Red Flags = Reliability
Today you'll learn the formula patterns that catch errors automatically. These controls prove Sarah's ledger is trustworthy to investors.
Purpose: Calculate total debits and credits for each account without manual addition.
Syntax: =SUMIF(range, criteria, [sum_range])
Example for Cash Debits: =SUMIF(LedgerTable[Account], "Cash", LedgerTable[Debit])
Example for Cash Credits: =SUMIF(LedgerTable[Account], "Cash", LedgerTable[Credit])
What it does: Sums all debit amounts where the account is "Cash". Repeat for credits.
Purpose: Verify that total debits equal total credits across all accounts.
Step 1: Calculate total debits: =SUM(LedgerTable[Debit])
Step 2: Calculate total credits: =SUM(LedgerTable[Credit])
Step 3: Compare: =IF(total_debits = total_credits, "Balanced", "Out of Balance")
What it does: Shows "Balanced" if debits equal credits, or "Out of Balance" if they don't.
Purpose: Color-code errors so Sarah spots them immediately.
Flag 1: Out-of-balance trial balance
Apply rule: ="Out of Balance" → format with red background and bold text
Flag 2: Negative balances (if not allowed)
Apply rule: <0 → format with red text
Flag 3: Blank or missing values
Apply rule: ="" → format with yellow background
What it does: Makes problems visible without manual scanning.
Problem: Formulas stop working when new rows are added to the table.
Cause: You used fixed ranges like A2:A100 instead of table column references.
Fix: Always use structured references like LedgerTable[Debit]. These automatically expand when new rows are added to the table.
Remember: Structured references = formulas that grow with your business.