How to calculate tiered sales commission in one formula
✓ Verified in LibreOffice 25.8.7.3Marginal commission tiers — each band pays its own rate, like tax brackets for sales.
The formula
| App | Formula | Notes |
|---|---|---|
| Excel | =SUMPRODUCT(--(A2>D2:D4),(A2-D2:D4),E2:E4) | D = tier thresholds (0, 10000, 25000), E = rate INCREASES at each threshold (0.05, 0.03, 0.02 for rates 5/8/10%). |
| Google Sheets | =SUMPRODUCT(--(A2>D2:D4),(A2-D2:D4),E2:E4) | Identical. |
| LibreOffice Calc | =SUMPRODUCT(--(A2>D2:D4),(A2-D2:D4),E2:E4) | Identical. |
How it works
Same machinery as the progressive-tax recipe: store each tier's rate INCREASE (5%, then +3% to reach 8%, then +2% to reach 10%) and let SUMPRODUCT apply each increase to everything above its threshold. $30,000 in sales: 30,000×5% + 20,000×3% + 5,000×2% = $2,200 — matching the tier-by-tier math (10k@5% + 15k@8% + 5k@10%). Cliff-style plans (whole amount at the highest tier's rate) are different and simpler: a closest-match lookup gets the rate, then multiply.
Verified, not just documented
We ran =SUMPRODUCT(--(A2>D2:D4),(A2-D2:D4),E2:E4) in LibreOffice 25.8.7.3 (headless, with forced recalculation) and it returned 2200 — exactly the expected result. Every formula here is confirmed by actually executing it.