Excel MMULT Function – Complete Guide




📊 Microsoft Excel — Math & Trig Advanced

Excel MMULT Function

Matrix multiplication inside Excel — multiplies two arrays row-by-column to produce a result matrix. Unlocks multi-dimensional weighted scoring, portfolio calculations, and bulk dot-product operations without helper columns.


📖 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)
123
456
2 rows × 3 cols
×
Array 2 (3×2)
78
910
1112
3 rows × 2 cols
=
Result (2×2)
5864
139154
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.

🔁 Related Topics & Functions
✖ SUMPRODUCT
🔄 TRANSPOSE
📐 MINVERSE (Matrix Inverse)
📐 MDETERM (Determinant)
➕ SUMIFS
🔢 Array Formulas (CSE)
📊 Dynamic Arrays (Excel 365)
📋 ROWS / COLUMNS
🏷 Named Ranges
🔄 SEQUENCE Function
⚡ Weighted Average
📈 Portfolio Analysis
tubeshaala · Excel Function Guide · MMULT · For educational use only

Scroll to Top