Two-Way Summary Table (Matrix Report)

All versionsSUMIFSMatrix

A two-way summary totals your data by two dimensions at once — region down the side, month across the top — building a matrix report without a PivotTable. The trick is one SUMIFS with cleverly locked references that you fill across and down.

Quick formula: with row labels in $A2 and column labels in B$1:

=SUMIFS(amount, region, $A2, month, B$1)

Lock the column of the row label ($A2) and the row of the column label (B$1) so one formula fills the whole grid.

Functions used (tap for the full reference guide):

SUMIFS SUMPRODUCT

The example

Raw rows (region, month, amount) summarized into a region × month grid.

	A	B	C	D
1		Jan	Feb	Mar
2	West	$120	$200	$150
3	East	$90	$70	$110

The formula

One formula, entered top-left and filled across and down:

=SUMIFS($D:$D, $B:$B, $A2, $C:$C, B$1) // region = row label, month = column label

How it works

Mixed references are the whole technique:

The criteria for the row dimension is $A2 — column locked ($A), row relative — so every cell in a row reads its own region label.
The criteria for the column dimension is B$1 — row locked ($1), column relative — so every cell in a column reads its own month header.
SUMIFS totals the amounts where both match — the West/Feb cell sums only West February rows.
Because the data ranges are fully locked ($D:$D etc.), the single formula works in every cell of the grid.

Excel 365 shortcut: =SUMIFS() still rules for a fixed grid, but for a self-building matrix try GROUPBY or PIVOTBY — one formula spills the entire labelled summary.

Try it: interactive demo

Live demo

Pick a region and month; see the matrix cell SUMIFS returns.

Region (row)

Month (column)

Cell total:

Variations

Add row and column totals

A SUM along each edge gives margins:

=SUM(B2:D2) // row total

Count instead of sum

Swap SUMIFS for COUNTIFS for a tally matrix:

=COUNTIFS($B:$B, $A2, $C:$C, B$1)

Self-building matrix (365)

PIVOTBY spills the whole labelled summary:

=PIVOTBY(region, month, amount, SUM)

Pitfalls & errors

Wrong cells fill in. The mixed references must be exactly $A2 (column-locked) and B$1 (row-locked). Getting the dollar signs wrong scrambles the grid.

Labels must match the data exactly. A header “Jan” won’t match data that says “January.” Keep them identical (or use a helper).

Whole-column ranges are convenient but slower on big sheets. Use bounded ranges ($D$2:$D$999) for performance, still fully locked.

Practice workbook

📊

Download the free Two-Way Summary Table (Matrix Report) practice workbook

Raw data plus a live SUMIFS region-by-month matrix with totals, the COUNTIFS variant, and 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I make a two-way summary table in Excel without a PivotTable?

Use one SUMIFS filled across and down: =SUMIFS($D:$D, $B:$B, $A2, $C:$C, B$1). Lock the row label's column ($A2) and the column header's row (B$1) so the single formula fills the whole grid.

How do I add row and column totals to the matrix?

Put a SUM along each edge, e.g. =SUM(B2:D2) for a row total and =SUM(B2:B3) for a column total.

Is there a one-formula way in Excel 365?

Yes, PIVOTBY or GROUPBY build a labelled summary that spills automatically: =PIVOTBY(region, month, amount, SUM).

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Function references: SUMIFS · SUMPRODUCT