Commission tiers, blank-tolerant aggregation and a recording that breaks monthly

Cause. The nested IF almost certainly tests > where the policy says "and above": IF(F2>100000,...) sends exactly 100,000 down to the next branch. With nine levels, one misplaced operator also shifts every tier below it, and because the formula is unreadable nobody notices. Excel returns the first TRUE branch, so the descending order must be preserved when repairing it.

Repair. Change each threshold to >=, working top-down, and test both boundary values (50,000 and 100,000) plus one value just either side of each after every edit. Store thresholds in cells rather than hard-coding them.

Better structure - a lookup table. Put ascending thresholds in a small table (0, 25,000, 50,000, 100,000 ...) with the rate beside each, then use =VLOOKUP(F2,$Tiers$A$2:$B$10,2,TRUE) or INDEX/MATCH with match type 1. Approximate match returns the largest threshold at or below the value, so "and above" is the default behaviour and cannot be got wrong.

Why not IFS: it would work on the Excel 2019 machines but is absent from the perpetual Excel 2016 copies half the recipients use, so the file would show #NAME?. The lookup table works on both and is auditable by finance.

Cause. SUMPRODUCT with * multiplies arrays, and multiplication coerces every element to a number. An empty string "" returned by a formula is text, so the whole array raises #VALUE!, even when only one cell is involved. Genuinely blank cells would be read as zero and would not fail; it is the zero-length string that breaks it.

Options:

1. Preferred - SUMIFS, which ignores text and blanks by design: =SUMIFS($Comm,$Rep,$A2,$Qtr,B$1). All ranges must be the same size. Use bounded ranges (not whole columns) or the three-criteria block becomes sluggish across 3,200 rows and many summary cells.
2. Keep SUMPRODUCT where OR logic is required, since SUMIFS cannot express OR, but make it text-proof: =SUMPRODUCT(--($Rep=$A2),--($Qtr=B$1),N($Comm)). N() maps text to 0 and numbers to themselves, and the double minus turns TRUE/FALSE into 1/0 without coercing the values.
3. Fix the source: return 0 rather than "" in cells that feed arithmetic, and use a custom number format such as 0.00;-0.00;"" to hide the zeros for display.

Verification: reconcile one rep-quarter against a manually filtered sum before the summary is circulated; otherwise a silently corrected formula can hide a genuine data problem.

Why it broke. Recording captured absolute addresses, so the macro replays "go to F3201" against a sheet that now ends at row 3,540. Recording also bakes in Select and Activate calls and the identity of the active sheet, so it behaves differently depending on which tab is showing when someone runs it.

Rewrite pattern:

Dim ws As Worksheet, lastRow As Long, rng As Range
Set ws = ThisWorkbook.Worksheets("Deals")
lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
Set rng = ws.Range("A2:M" & lastRow)
ws.Calculate

• End(xlUp) from the bottom of a key column finds the true last row; avoid UsedRange, which keeps stale formatted rows alive.
• Work through object variables and qualify every Range with its sheet; never rely on Selection.
• Read the eight recipient addresses from a table instead of embedding them in code.
• Put the PDF export in its own procedure with On Error GoTo handling, so a failed Outlook call does not leave a half-written file behind.

Deployment. Save as .xlsm, keep the master as an .xltm template so each month starts clean, and sign the VBA project so recipients are not stopped by the security bar.