Matrix Manipulation Formula Engine

Overview

Excel MMULT SUMIFS LAMBDA GROUPBY

This demo is based on the Matrix Manipulation workbook I developed at SumProduct. It showcases 8 distinct Excel formula approaches to the same problem: aggregating a categorised data array (categories A, B, C across 10 periods) into a summary matrix. Each method produces identical results but uses fundamentally different formula architectures — from classic MMULT matrix multiplication to modern GROUPBY and LAMBDA functions.

Download the Excel file (.xlsm) to explore the full workbook including 1D, 3D, and 4D array techniques.

The 8 Formula Approaches

1. MMULT (Matrix Multiplication)

Classic approach · Uses a boolean helper matrix to perform aggregation via matrix multiplication

2. DROP REDUCE SUMIFS LAMBDA

Dynamic arrays · Iterates periods with REDUCE, applies SUMIFS per column, stacks with HSTACK

3. MAP LAMBDA

Contributed by Kris · Concatenates category-period keys, maps SUM over matching combinations

4. REDUCE BYCOL LAMBDA

Contributed by Kris · Stacks rows via REDUCE, aggregates each column with BYCOL

5. MAKEARRAY LAMBDA

Dynamic arrays · Builds the result matrix cell-by-cell using row/column index LAMBDA

6. GROUPBY

Contributed by Kris · Newest Excel function — one-line aggregation by category

7. SUBTOTAL + OFFSET

Legacy compatible · Requires pre-sorted data; uses OFFSET to define dynamic ranges per category

8. SUMIFS + OFFSET

Contributed by Oscar · Uses OFFSET to shift the sum range column-by-column with SUMIFS criteria

Live Excel Workbook

Explore the actual workbook directly in your browser via Excel Online:

Interactive Formula Demo

Switch between formula methods to see how each one aggregates the same source data. Click any result cell to see its specific formula and which source rows contribute.

When to Use Which

The choice depends on your Excel version and model requirements:

GROUPBY — Simplest syntax, but requires Microsoft 365 (2024+). Best for quick ad-hoc analysis.
MMULT — Works in older Excel versions. Ideal for financial models where auditors expect matrix algebra patterns.
LAMBDA variants — Most flexible; handles edge cases well. Best when you need custom aggregation logic beyond SUM.
SUBTOTAL + OFFSET — Legacy approach for pre-365 environments. Requires sorted data but works everywhere.
SUMIFS + OFFSET — Good middle ground; auditable and works in Excel 2016+.

I use these patterns regularly at SumProduct for multi-dimensional financial model aggregation, variance analysis, and reporting matrices.