fx  |  A1:Z999  |  Ledger Reference

1. SUM, SUMIF & SUMIFS

SUM totals a range and quietly ignores text or blanks, which is why it’s safe even in messy ledgers. SUMIF totals values that meet one condition — say, every expense tagged “Travel.” SUMIFS extends this to multiple conditions at once, such as total sales for one client in one month.

=SUMIFS(sum_range, range1, “criteria1”, range2, “criteria2”)

Rule: in SUMIFS the sum range comes first; in SUMIF it comes last. Mixing this up is the most common error when switching between the two.

Both formulas also support wildcard criteria — "Travel*" catches every ledger label starting with “Travel,” which is handy when descriptions aren’t perfectly standardized across entries.

2. VLOOKUP, INDEX-MATCH & XLOOKUP

VLOOKUP pulls data from another table but only searches rightward from its lookup column, and its column-number reference breaks the moment someone inserts a column. INDEX-MATCH fixes both problems: it can look in either direction and survives structural changes because it references ranges instead of column positions.

=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))

Rule: always force exact match — FALSE in VLOOKUP, 0 in MATCH. Leaving that argument blank risks an approximate match that silently returns the wrong figure instead of an obvious error.

Where available, XLOOKUP is now the recommended default over both: it searches in either direction, defaults to exact match without needing an extra argument, and lets you specify a clean fallback value instead of returning #N/A when nothing is found.

3. IF, IFS & IFERROR

IF flags conditions — overdue invoices, breached thresholds, pass/fail checks. Nesting IF inside IF beyond two or three levels becomes unreadable; IFS handles multiple conditions cleanly instead, evaluating top to bottom and stopping at the first true result.

Rule: always add a final catch-all condition in IFS (TRUE, "value") to avoid errors when nothing matches.

Use IFERROR only for genuinely valid error cases — like a ratio whose denominator can legitimately be zero — never simply to mask a broken formula. Wrapping a faulty VLOOKUP in IFERROR just to hide the #N/A turns a visible problem into an invisible one, which is worse for anyone reviewing the file later.

4. COUNTIF & COUNTIFS

These are control-check formulas: counting duplicate invoice numbers, transactions above a threshold, or entries missing a cost-center code. A classic duplicate-detection pattern:

=COUNTIF(A:A, A2) > 1

COUNTIFS extends the same logic across multiple criteria — for example, counting how many purchase orders exceed a spending limit and were approved by a specific person, a common internal-control check during an audit.

5. Rounding & Precision

ROUND, ROUNDUP, and ROUNDDOWN control how decimals resolve; MROUND rounds to a chosen multiple; TRUNC simply cuts decimals without rounding.

Rule: a trial balance that’s “off by a cent” almost always traces back to display formatting hiding unrounded numbers underneath. Round the actual value with ROUND, never just the display — Excel still calculates with full precision behind a formatted cell, so two figures that appear to add up on screen can still be a fraction off in the underlying total.

6. Date Functions

EOMONTH finds the last day of a month — core to month-end close and depreciation schedules. EDATE shifts a date forward or back by months, useful for loan maturities. DATEDIF measures tenure or days overdue. NETWORKDAYS counts working days for payroll and interest calculations.

Rule: store dates as real date values, never typed as text. A text-formatted date breaks every date function and every SUMIFS or COUNTIFS built on a date range — and it often does so silently, returning zero instead of an obvious error, which makes it easy to miss during review.

7. Financial Functions

PMT calculates fixed loan or EMI payments. NPV discounts future cash flows to present value for capital budgeting. IRR finds the discount rate where NPV equals zero, testing whether a project clears its cost of capital. FV and PV project or discount single lump sums, common in lease and retirement schedules.

Rule: match the rate and period unit in PMT — divide an annual rate by 12 and multiply the term in years by 12 for monthly payments; mismatching these is the single most common PMT error. NPV assumes the first cash flow lands one period from now, so an upfront investment made today at time zero should be added separately, outside the NPV range, rather than folded into the list of cash flows.

8. Text Cleanup

Real bank and ERP exports are rarely clean. TRIM removes stray spaces that silently break lookups. TEXTJOIN combines fields into references. LEFT, RIGHT, and MID extract parts of a code. TEXT formats numbers or dates for reports, and VALUE converts numbers stored as text back into real numbers.

Rule: if a SUM or SUMIF returns zero despite visible numbers, the range is almost certainly stored as text — a common side effect of CSV or ERP exports where numbers arrive left-aligned instead of right-aligned. Fix the data with VALUE or Text-to-Columns before assuming the formula itself is broken; rewriting a correct formula rarely solves a data-type problem.

9. Reconciliation

SUMPRODUCT handles conditions SUMIFS can’t, like summing rows where a calculated value crosses a threshold. A standard two-way reconciliation pattern compares totals from two independent sources by a shared key:

=SUMIF(LedgerA_range, key, LedgerA_amt) − SUMIF(LedgerB_range, key, LedgerB_amt)

10. Beyond Formulas: Pivot Tables

Not every summarization problem needs a formula. When the task is grouping a large transaction list by account, period, or department and getting instant subtotals, a Pivot Table often does the job faster and with less risk of a broken reference than a wall of SUMIFS formulas.

Rule: keep the source data as a proper Excel Table before building a Pivot Table on top of it. That way, when next month’s transactions are appended, the Pivot Table’s source range grows automatically instead of quietly excluding new rows.

General Rules to Live By

1. Use absolute references ($A$1) for constants like tax or exchange rates.
2. Never hardcode assumptions inside a formula — put them in a labeled cell and reference it.
3. Match units before comparing figures — monthly vs. annual, or mismatched currencies, distort results.
4. Prefer Excel Tables over plain ranges so formulas auto-expand as rows are added.
5. Audit with Ctrl + ~ to view formulas before finalizing anything for external review.
6. Protect calculation cells once a workbook is finalized.
7. Document non-obvious logic with comments — future readers, including future you, will need it.