| Formula | Use |
|---|---|
=SUMIFS(sum, crit_range, crit, ...) | Budget actuals by category and month |
=INDEX(return, MATCH(key, lookup, 0)) | Still the reliable lookup |
=IFERROR(x, "") | Clean dashboards |
=TEXT(date, "yyyy-mm") | Stable month keys |
=XLOOKUP(...) | Test on your build before you rewrite the workbook |
=UNIQUE / =FILTER | Same warning — 365-era functions vary |
Always set a known-good total in a cell and compare. AI-suggested formulas are drafts.
Patterns, not trivia
=SUMIFS(Amount, Category, $G2, Month, H$1) is a dashboard. Copy it across a grid of categories × months. If #VALUE, Amount has text. If zeros everywhere, Month in the sheet is “Jan” and the header is “2026-01” — keys must match. =IFERROR(INDEX(... MATCH(...,0)), "") hides #N/A for humans but also hides real misses — keep a count of unmatched keys somewhere visible.
Volatile functions
NOW, TODAY, RAND, OFFSET, INDIRECT recalc often. A sheet full of them on a weak PC stutters. Prefer INDEX and bounded ranges. TODAY in a print-once invoice is fine; TODAY in 50,000 rows is not.
Audit a sheet you did not build
Find all formulas: jump to special if offered, or click cells that look like totals and read the formula bar. Trace a known-good number backwards. If you see #REF!, someone deleted a source. If you see numbers typed over formulas, the sheet is already a lie — copy values to an archive and rebuild Month.