Excel MMULT Function

Excel MMULT Function – Complete Guide

📖 What is the MMULT Function?

MMULT stands for Matrix Multiplication. It takes two numeric arrays (matrices) and returns a new array where each element is the dot product of the corresponding row from the first array and column from the second array. It is categorised under Math & Trigonometry and is one of Excel's true array-returning functions.

In practical terms, MMULT enables you to multiply quantities by prices across multiple categories simultaneously, compute weighted scores for multiple candidates in one formula, or perform portfolio return calculations — all without helper columns or repeated SUMPRODUCT calls. The result is always an array whose dimensions are determined by the input matrix sizes.

Array 1 (2×3)

2 rows × 3 cols

Array 2 (3×2)

3 rows × 2 cols

Result (2×2)

139

154

2 rows × 2 cols

📐 The Golden Rule of Matrix Multiplication

Columns of Array1 must equal Rows of Array2. The result matrix dimensions are: Rows of Array1 × Columns of Array2

✔ Valid — (3×2) × (2×4) = (3×4)

Array1 has 2 cols, Array2 has 2 rows → match ✔

✔ Valid — (5×3) × (3×1) = (5×1)

Array1 has 3 cols, Array2 has 3 rows → match ✔

✗ Invalid — (3×4) × (3×2)

Array1 has 4 cols, Array2 has 3 rows → mismatch → #VALUE!

✗ Invalid — (2×3) × (2×3)

Array1 has 3 cols, Array2 has 2 rows → mismatch → #VALUE!

⚙️ Syntax & How It Computes

=MMULT( array1, array2 )

array1 → Required. First matrix — numeric cells only, no blanks or text. (m × n dimensions)

array2 → Required. Second matrix — columns of array1 must = rows of array2. (n × p dimensions)

── Result dimensions ────────────────────────────────────────────

array1 is (m × n) and array2 is (n × p) → result is (m × p)

── Common usage patterns ────────────────────────────────────────

=MMULT(A2:C4, E2:F4) → Multiply two cell ranges

=MMULT(weights, scores) → Named range weighted scoring

=MMULT(A2:C2, TRANSPOSE(A2:C2)) → Dot product of a row vector

=MMULT(qty_matrix, price_matrix) → Multi-product revenue in one step

🔢 How Each Result Cell Is Computed

Result[row i, col j] = SUM( Array1[i, k] × Array2[k, j] ) for all k

Example: Result[1,1] = (1×7) + (2×9) + (3×11) = 7 + 18 + 33 = 58

Example: Result[1,2] = (1×8) + (2×10) + (3×12) = 8 + 20 + 36 = 64

ℹ️ Array entry: In Excel versions before 365, MMULT must be entered as an array formula — first select the output range matching the result dimensions, type the formula, then press Ctrl + Shift + Enter. In Excel 365, dynamic arrays handle this automatically — just press Enter in the top-left cell.

📊 Example 1 — Multi-Product Revenue: Qty × Price Matrix

Basic Use Case

A retail business sells 3 products across 2 stores. Quantity data is in a 3×2 matrix (products × stores) and unit prices are in a 3×1 column. MMULT multiplies each product's quantity by its price and sums across all products — giving total revenue per store in one formula.

Array 1 — Quantities (3×2)

Product	Store A	Store B
Laptop	10	8
Mobile	25	30
Tablet	15	12

Array 2 — Unit Price (3×1)

Product	Price (₹)
Laptop	55,000
Mobile	18,000
Tablet	32,000

Store A Revenue:
  (10 × 55,000) + (25 × 18,000) + (15 × 32,000)
  = 5,50,000 + 4,50,000 + 4,80,000 = ₹ 14,80,000

Store B Revenue:
  (8 × 55,000) + (30 × 18,000) + (12 × 32,000)
  = 4,40,000 + 5,40,000 + 3,84,000 = ₹ 13,64,000

Select result range H2:H3 (2 rows × 1 col) then enter:
=MMULT(TRANSPOSE(B2:C4), E2:E4)
// TRANSPOSE flips qty matrix to (2×3) so cols match price rows (3×1) → result (2×1)

Or directly (3×2 qty × price as column):
=MMULT(TRANSPOSE(B2:C4), E2:E4) → ₹ 14,80,000 | ₹ 13,64,000

✅ Store A: ₹ 14,80,000 🏪 Store B: ₹ 13,64,000

Why MMULT beats SUMPRODUCT here: For 2 stores, you'd need 2 separate SUMPRODUCT formulas. For 10 stores, you'd need 10. MMULT computes all stores simultaneously in one formula — the formula stays the same regardless of how many stores you add.

📊 Example 2 — Weighted KPI Scorecard for Multiple Employees

Advanced / Practical Use Case

An HR department evaluates 4 employees across 3 KPIs. Each KPI has a different weightage (totalling 100%). Using MMULT, compute each employee's weighted total score in one formula — no helper columns, no repeated SUMPRODUCT per employee.

Scores Matrix — Employees × KPIs (4×3)

Employee	Sales	Service	Compliance
Amit Sharma	85	78	90
Priya Mehta	92	88	76
Rohan Gupta	70	95	88
Sunita Nair	88	82	94

Weights (3×1)

KPI	Weight
Sales	0.50
Service	0.30
Compliance	0.20

Amit Sharma: (85×0.50) + (78×0.30) + (90×0.20) = 42.5 + 23.4 + 18.0 = 83.9
Priya Mehta: (92×0.50) + (88×0.30) + (76×0.20) = 46.0 + 26.4 + 15.2 = 87.6
Rohan Gupta: (70×0.50) + (95×0.30) + (88×0.20) = 35.0 + 28.5 + 17.6 = 81.1
Sunita Nair: (88×0.50) + (82×0.30) + (94×0.20) = 44.0 + 24.6 + 18.8 = 87.4

Select H2:H5 (4 rows × 1 col), then:
=MMULT(B2:D5, G2:G4)
// Scores (4×3) × Weights (3×1) → Result (4×1) — one weighted score per employee

In Excel 365 (single cell, spills automatically):
=MMULT(B2:D5, G2:G4) → 83.9 | 87.6 | 81.1 | 87.4

🥇 Priya Mehta: 87.6 🥈 Sunita Nair: 87.4 🥉 Amit Sharma: 83.9

Scalability insight: Add 20 more employees — the formula =MMULT(B2:D25, G2:G4) handles all of them. No extra columns, no formula changes. In contrast, SUMPRODUCT would require one formula per employee. For large appraisal cycles, MMULT delivers weighted scores for hundreds of employees in a single array formula.

💡 Key Applications

HR Bulk weighted appraisal scoring — multiply employee score matrices by KPI weight vectors to rank all employees simultaneously in one formula for annual performance reviews.
Finance Portfolio return calculation — multiply asset weight matrix by return matrix to compute weighted returns across multiple portfolios or time periods in one MMULT call.
Retail Multi-store, multi-product revenue — quantity matrix (products × stores) multiplied by price column returns total revenue per store without any helper columns.
MIS Contribution margin analysis — multiply units sold matrix by contribution per unit vector to get total contribution across multiple regions simultaneously in MIS dashboards.
Analytics Unique row count trick — =MMULT((A2:A100=TRANSPOSE(A2:A100))*1, ROW(A2:A100)^0) counts how many times each value appears, enabling duplicate frequency analysis without COUNTIF.
Operations Resource allocation matrix — multiply task-hours matrix by hourly rate vector to compute total cost per project across multiple departments simultaneously.
Audit Cross-tab verification — multiply a binary indicator matrix by values to compute conditional sums across multiple categories simultaneously — a powerful cross-checking tool for large data audits.

⚠️ Common Mistakes to Avoid

1. Dimension Mismatch — Most Common #VALUE! Error

Writing =MMULT(A2:C4, E2:G4) where Array1 has 3 columns but Array2 also has 3 rows — wait, that's valid. The error occurs when Array1's column count does NOT equal Array2's row count. E.g., =MMULT(A2:C4, E2:F3) — Array1 is (3×3) but Array2 is (2×2): 3 cols ≠ 2 rows → #VALUE!

✔ Fix: Always verify: cols(Array1) = rows(Array2). Use =COLUMNS(Array1) and =ROWS(Array2) to cross-check before building the formula.

2. Blank or Text Cells in Arrays → #VALUE! Error

Any blank cell or text entry (e.g., "N/A", headers accidentally included) inside either array causes MMULT to return a #VALUE! error. MMULT requires purely numeric arrays with no exceptions.

✔ Fix: Ensure arrays start from the first data row (not the header row). Replace blanks with 0. Use =IFERROR(array*1, 0) to coerce non-numeric values to zero before passing to MMULT.

3. Forgetting Ctrl+Shift+Enter in Excel 2016 and Earlier

In Excel versions before 365, MMULT returns an array result. If entered with just Enter in a single cell, only the first element of the result displays. The remaining cells show errors or stay empty.

✔ Fix: In Excel 2016/2013: select the full output range first, type the formula, then press Ctrl + Shift + Enter. In Excel 365, just press Enter — dynamic arrays handle spilling automatically.

4. Using MMULT for Simple Weighted Sum (SUMPRODUCT Is Better)

Using MMULT for a single-row weighted sum — =MMULT(scores, TRANSPOSE(weights)) — adds unnecessary complexity. SUMPRODUCT is simpler, more readable, and doesn't require managing dimensions for a single-employee, single-row calculation.

✔ Fix: Use SUMPRODUCT for a single weighted sum: =SUMPRODUCT(B2:D2, weights). Use MMULT only when computing the same operation across multiple rows simultaneously.

5. Confusing Result Dimensions — Selecting Wrong Output Range

Selecting a 3-row output range when the MMULT result is a 4×1 array causes the 4th value to spill into an unintended cell (Excel 365) or be cut off (older Excel). In complex dashboards, this overwrites data silently.

✔ Fix: Always pre-calculate result dimensions: result rows = rows(Array1), result cols = cols(Array2). Select exactly that range before entering the formula in non-365 Excel.