The server fan purred like a smug cat while my spreadsheet screamed like a kettle. I took a breath, slid on my neon headset, and began hammering chaos into clean columns as if I’d brought a plasma torch to a paperclip fight.
✅ Executive Snapshot: What You’ll Forge
Priya—my work besty and voice of reason—peeked over my monitor. “Make it sing, Diva, but keep it fast.” Right. Here’s the compact promise of what you’ll walk away with, minus the fluff and faux-inspiration.
- • Tame multi-criteria totals with SUMPRODUCT that slice by dates, flags, regions, and donor types—without helper columns.
- • Replace VLOOKUP forever using XLOOKUP that returns defaults, matches multiple criteria, and shrugs at re-ordered columns.
- • Build dynamic views with FILTER that refresh on the fly, powering dashboards that don’t cry under pressure.
- • Combine functions like a mech suit—FILTER to narrow, XLOOKUP to enrich, SUMPRODUCT to sum—with LET for speed and elegance.
- • Harden your formulas against dodgy data, weird blanks, and chaotic human input using IFERROR, ISNUMBER, and validation.
- • Boost performance and security for workbooks that travel across shared drives, OneDrive, and… the wilds of your inbox.
Validation: Narrative precedes advice; emoji heading used; list items concise and actionable.
🔧 SUMPRODUCT: The Hammer That Never Misses
The donor report was a junk drawer: tabs named “Final2v3THISONE.xlsx”, dates formatted like birthday wishes, and amounts that sometimes arrived with a stray space. Priya set down a coffee and whispered, “No helper columns, promise?” Challenge accepted. SUMPRODUCT time.
Actionable patterns you can use now:
- • Purpose: Calculate totals across multiple criteria—dates, categories, regions—using clean logic.
- • Ground rule: Convert TRUE/FALSE to 1/0 using `*` or `--`; multiply conditions to “AND”, add to “OR”.
Basic multi-criteria sum (using an Excel Table named Transactions):
Using `--` to coerce booleans if you prefer:
Add date boundaries—say, the current financial year:
AND plus OR—WA or SA, Status Active:
With LET for speed and readability (critical when the sheet bulks out):
Notes for precision:
- • Text numbers?: Wrap numeric columns with VALUE if imports misbehave:
```
=SUMPRODUCT((Transactions[Region]="WA")*VALUE(Transactions[Amount]))
``` - • Empty cells or hidden spaces?: CLEAN and TRIM on data import, or use “Text to Columns” to normalise.
- • Large ranges? Tables only. SUMPRODUCT across whole columns like A:A is a one-way ticket to fan-noise hell.
Validation: Scene first; examples tied to advice; formatting and emoji heading valid.
🧲 XLOOKUP: The Homing Beacon That Finds the Right Row, Every Time
Baz from Finance dropped a file where donor IDs were “helpfully” in column G this week. Last week? Column B. Did I look bothered? No, because XLOOKUP is a homing beacon that doesn’t care where the target column hides.
Actionable XLOOKUP patterns:
- • Basic, with default if missing:
```
=XLOOKUP(A2, Donors[Donor ID], Donors[Status], "Not found")
``` - • Exact vs approximate: The `match_mode` parameter matters:
- 0 = exact (default), -1 = exact or next smaller, 1 = exact or next larger, 2 = wildcard.
```
=XLOOKUP(A2, Rates[Threshold], Rates[Rate], "No rate", 1) // approximate ascending
``` - • Wildcard magic for partial matches:
```
=XLOOKUP(""&E2&"", Campaigns[Name], Campaigns[Code], "No match", 2)
``` - • Multi-criteria lookup by concatenating keys:
```
=XLOOKUP(A2&B2, CHOOSE(1,Donations[Donor ID]&Donations[Date]), Donations[Amount], 0)
```
Or, create a helper column in the source Table with `[Key]=[Donor ID]&[Date]` and match against that. - • Return multiple columns at once (spill arrays):
```
=XLOOKUP(A2, Donors[Donor ID], CHOOSECOLS(Donors, 2, 4, 6), "Missing")
``` - • Fallback chain—look in current list, else archived list:
```
=LET(
id, A2,
cur, XLOOKUP(id, DonorsLive[Donor ID], DonorsLive[Email], ""),
IF(cur<>"", cur, XLOOKUP(id, DonorsArchive[Donor ID], DonorsArchive[Email], "No email"))
)
```
Hard truths:
- • Never rely on VLOOKUP column indexes—it’s brittle. XLOOKUP references the exact return column.
- • Always specify a default. If you don’t, you’ll babysit #N/A like it’s your new job title.
- • Normalise the key (UPPER, TRIM) on both sides when imports are feral.
Validation: Narrative present; advice stems from scene; emoji heading and fenced code valid.
🌊 FILTER: The Sieve for Real-Time Views
The dashboard screen glowed like an arcade cabinet. I nudged slicers; Priya watched rows shimmer in and out. “Can this show WA donors over $1,000 who gave in the last quarter?” Darling, say less.
Actionable FILTER constructs:
- • Basic filter by single condition:
```
=FILTER(Transactions, Transactions[Region]="WA", "No WA rows")
``` - • Multiple conditions (AND):
```
=FILTER(
Transactions,
(Transactions[Region]="WA")(Transactions[Amount]>=1000)(Transactions[Date]>=EOMONTH(TODAY(),-3)+1),
"No matches"
)
``` - • Multiple conditions (OR):
```
=FILTER(Transactions, (Transactions[Region]="WA")+(Transactions[Region]="SA"), "None")
``` - • Return selected columns only:
```
=LET(
t, FILTER(Transactions, Transactions[Status]="Active", ""),
CHOOSECOLS(t, 1, 3, 5)
)
``` - • Unique + sorted view (for dropdowns):
```
=SORT(UNIQUE(FILTER(Transactions[Donor ID], Transactions[Amount]>=1000)))
``` - • Dynamic last N days:
```
=FILTER(Transactions, Transactions[Date]>=TODAY()-30, "No recent")
```
Spill etiquette:
- • Leave space for the spill range; or anchor it with `#` in dependent formulas:
```
=SUM(FILTER(Transactions[Amount], Transactions[Region]="WA")#)
``` - • Wrap with IFERROR when optional filters might return nothing:
```
=IFERROR(FILTER(Transactions, Transactions[Region]="NT"), "")
```
Validation: Narrative first; actionable patterns linked; emoji heading present; code fenced.
🧩 Combo Moves: Forge Pipelines That Feel Like Magic
The brief: “Make a one-sheet view where staff type a donor ID, and it spits out: donor bio, last three gifts, and total given YTD.” Priya raised an eyebrow. “One sheet.” Challenge accepted.
Pattern: FILTER to narrow → XLOOKUP to enrich → SUMPRODUCT to aggregate.
- • User enters donor ID in H2. Get profile:
```
=XLOOKUP(H2, Donors[Donor ID], CHOOSECOLS(Donors, SEQUENCE(1,5)), "Unknown")
``` - • Last three gifts:
```
=LET(
d, FILTER(Transactions, Transactions[Donor ID]=H2, ""),
SORT(d, XMATCH(d[Date], SORT(UNIQUE(d[Date]),,-1)), -1),
CHOOSEROWS(d, 1, 2, 3)
)
```
If XMATCH/CHOOSEROWS not available, sort by Date descending and use INDEX/SEQUENCE. - • YTD total:
```
=LET(
a, Transactions[Amount],
id, Transactions[Donor ID],
dt, Transactions[Date],
SUMPRODUCT((id=H2)(YEAR(dt)=YEAR(TODAY()))a)
)
``` - • One-click region summary for that donor:
```
=LET(
d, FILTER(Transactions, Transactions[Donor ID]=H2, ""),
r, d[Region],
a, d[Amount],
SORTBY(UNIQUE(r), -SUMIFS(a, r, UNIQUE(r)))
)
```
Why this rocks:
- • Small inputs, large outputs. Let the sheet do the walking.
- • No fragile helper columns. Your logic lives where it’s used.
- • Extensible: Add new bits without rewriting a labyrinth.
Validation: Scene precedes advice; combo patterns concrete; code fenced; emoji heading correct.