The first thing most people do when they try MMULT is wrap it around two ranges that look vaguely rectangular and hit Enter. Then they get #VALUE! — and assume Excel is broken. It’s not. MMULT doesn’t care about labels, names, or even whether your numbers ‘make sense’ — it only checks one thing: matrix dimensions. Get that wrong, and nothing else matters.
The Setup
We’re calculating quarterly commission payouts for a small SaaS reseller team at Alibaba Cloud Partners Group. Each rep sells three product tiers (Starter, Pro, Enterprise), and commissions vary by both tier and region. The raw data lives in two sheets: RepSales (A1:D9) and CommissionRates (F1:H4).
| Rep Name | Starter Units | Pro Units | Enterprise Units |
|---|---|---|---|
| Sarah Chen | 12 | 8 | 3 |
| Diego Mora | 7 | 14 | 5 |
| Priya Nair | 0 | 19 | 9 |
| Kenji Tanaka | 15 | 6 | 2 |
| Amina Diallo | 9 | 11 | 4 |
| Luca Rossi | 5 | 13 | 7 |
| Maya Patel | 11 | 4 | 6 |
| Tariq Hassan | 3 | 16 | 1 |
And here’s the rate table — note the order matches: Starter, Pro, Enterprise across columns, and regions down rows:
| Region | Starter | Pro | Enterprise |
|---|---|---|---|
| APAC | 0.03 | 0.05 | 0.08 |
| EMEA | 0.025 | 0.045 | 0.075 |
| Americas | 0.035 | 0.055 | 0.085 |
The Challenge
We need total commission per rep by region. So Sarah Chen’s APAC commission = (12 × 0.03) + (8 × 0.05) + (3 × 0.08) = $0.92. But we have 8 reps and 3 regions — that’s 24 calculations. Doing this manually would take 4+ minutes and invite copy-paste errors. And SUMPRODUCT? It only gives one result per row — not a full 8×3 grid. That’s where MMULT shines. But only if you respect its rules.
The catch: MMULT(array1, array2) requires array1 to have the same number of columns as array2 has rows. Our sales data is 8×3 (8 reps, 3 tiers). Our rates are 3×3 (3 regions, 3 tiers). So to get an 8×3 output (reps × regions), we need to flip the rates — transpose them so they become 3×3 → 3×3 stays valid, but orientation matters. Wait — actually, no. Let’s test it.
Walking Through It
Step 1: Select the output range. Highlight J2:L9 — that’s 8 rows × 3 columns. Don’t type anything yet.
Step 2: Type =MMULT(A2:C9,F2:H4). A2:C9 is 8×3. F2:H4 is 3×3. So 8×3 × 3×3 = 8×3. Perfect. Press Ctrl+Shift+Enter (or Ctrl+Enter in newer Excel versions with dynamic arrays). You’ll see braces {} appear automatically in the formula bar — that’s how you know it’s an array formula.
But hold on — that gives wrong numbers. Why? Because F2:H4 lists regions vertically but MMULT multiplies row × column. So row 1 of A2:C9 (Sarah’s units) gets multiplied against column 1 of F2:H4 — which is Starter rates, not APAC. We need APAC rates to be the first column after transposition.
So Step 2 corrected: Use =MMULT(A2:C9,TRANSPOSE(F2:H4)). Now F2:H4 (3×3) becomes 3×3 transposed → still 3×3, but now columns become rows. Wait — no. Transpose of 3×3 is still 3×3, but orientation flips. Actually: F2:H4 is Region (rows) × Tier (columns). We want Tier (rows) × Region (columns), so TRANSPOSE(F2:H4) gives us exactly that: 3 tiers down, 3 regions across.
Try it: Select J2:L9, type =MMULT(A2:C9,TRANSPOSE(F2:H4)), then press Ctrl+Shift+Enter. Now the numbers line up.
| Rep Name | APAC | EMEA | Americas |
|---|---|---|---|
| Sarah Chen | 0.92 | 0.85 | 1.00 |
| Diego Mora | 0.86 | 0.79 | 0.94 |
| Priya Nair | 1.31 | 1.21 | 1.43 |
| Kenji Tanaka | 0.91 | 0.84 | 1.00 |
| Amina Diallo | 0.86 | 0.79 | 0.94 |
| Luca Rossi | 1.07 | 0.99 | 1.17 |
| Maya Patel | 0.97 | 0.89 | 1.06 |
| Tariq Hassan | 1.04 | 0.96 | 1.14 |
The Result
Here’s the final output — clean, scalable, and recalculates instantly if any unit count or rate changes. No dragging, no hidden references, no risk of misaligned rows. This is why finance teams at Alibaba Cloud Partners use MMULT for channel payout models — not because it’s flashy, but because it’s bulletproof once you nail the shape logic.
| Rep Name | APAC | EMEA | Americas |
|---|---|---|---|
| Sarah Chen | $0.92 | $0.85 | $1.00 |
| Diego Mora | $0.86 | $0.79 | $0.94 |
| Priya Nair | $1.31 | $1.21 | $1.43 |
| Kenji Tanaka | $0.91 | $0.84 | $1.00 |
| Amina Diallo | $0.86 | $0.79 | $0.94 |
| Luca Rossi | $1.07 | $0.99 | $1.17 |
| Maya Patel | $0.97 | $0.89 | $1.06 |
| Tariq Hassan | $1.04 | $0.96 | $1.14 |
What Could Go Wrong
Here are the three mistakes I saw in the last three finance reviews — all from well-meaning analysts who’d read the Excel Help page but never tested shapes:
| Symptom | Cause | Fix |
|---|---|---|
#VALUE! across entire output | One array contains text (e.g., a header cell accidentally included like A1:C9 instead of A2:C9) | Select ranges manually — don’t click-and-drag headers. Use Ctrl+→ then Shift+↓ to extend cleanly. |
#N/A in top-left corner only | Output range was too small (J2:L5 instead of J2:L9) | Always select full target range *before* typing the formula. Excel won’t auto-expand unless you’re on Microsoft 365 with dynamic arrays. |
| Numbers exist but don’t match manual calc | Rates weren’t transposed — multiplication aligned tiers × tiers instead of tiers × regions | Add TRANSPOSE() around the second array *unless* your rate table is already Tier-down × Region-across. |
Surprising tip: If you’re on Excel for Microsoft 365, skip Ctrl+Shift+Enter. Just press Enter. But — and this is critical — if you later open the file in Excel 2019 or earlier, those formulas will break unless you re-enter them as legacy array formulas. Always check your audience’s version before deploying.
Next step: Open your commission model. Find the first MMULT formula. Replace it with =MMULT(--(A2:C9),TRANSPOSE(--(F2:H4))). The double-unary (--) forces text numbers (like “12” stored as text) into real numbers — a silent killer in imported CRM exports. Then press Ctrl+Shift+Enter.
