Two Way Summary with Dynamic Arrays

This article explains how to create a two-way summary (a cross-tabulation or pivot-style table) using dynamic array formulas in Excel, Google Sheets, and similar spreadsheet software. You enter a formula that spills results across rows and columns, and it automatically updates when your source data changes, without needing to drag, copy, or refresh.

Formula

The core formula for a two-way summary with dynamic arrays uses UNIQUE to extract row and column headers, and SUMIFS (or COUNTIFS, AVERAGEIFS, etc.) to aggregate values. In Excel, the formula typically looks like:

=SUMIFS(values, rowField, rowHeader, colField, colHeader)

where values is the range to sum, rowField is the column containing row categories, rowHeader is a single cell from the spilled row list, colField is the column containing column categories, and colHeader is a single cell from the spilled column list. In Google Sheets, you can use QUERY or ARRAYFORMULA with SUMIF to achieve the same result.

Worked Example

Suppose you have sales data with columns: Product (A), Region (B), and Sales (C). You want a table with products as rows and regions as columns, showing total sales.

1. In cell E2, enter =UNIQUE(A2:A100) to get the list of products (spills down).
2. In cell F1, enter =TRANSPOSE(UNIQUE(B2:B100)) to get regions across the top (spills right).
3. In cell F2, enter =SUMIFS($C$2:$C$100, $A$2:$A$100, $E2, $B$2:$B$100, F$1) and press Enter. The formula will spill to fill the entire table.

For the default values in the calculator above (finish time 50 min, distance 10 km), the average pace is 5.00 min/km and speed is 12.00 km/h. In a two-way summary, the same logic applies: the formula references the row and column headers to compute each cell.

Why Dynamic Arrays Matter

Traditional two-way summaries required either manual formulas copied across every cell, or a PivotTable that needed manual refresh. Dynamic arrays, introduced in Excel 365 and available in Google Sheets, let a single formula return an entire table. This means:

• No more dragging formulas across hundreds of cells.
• When source data changes, the summary updates automatically if the ranges are dynamic (e.g., using FILTER or structured references).
• You can combine multiple aggregation functions (SUM, COUNT, AVERAGE) in one formula using HSTACK or CHOOSE.

How to Choose the Right Function

The choice of aggregation function depends on what you need:

• SUMIFS – for totals of numeric values.
• COUNTIFS – for counts of records.
• AVERAGEIFS – for averages.
• MAXIFS/MINIFS – for maximum/minimum values.
• UNIQUE – to generate the list of categories.

In Google Sheets, QUERY is often more powerful, allowing you to write a single formula like =QUERY(A:C, "select A, sum(C) where A is not null group by A pivot B") which directly creates a two-way summary.

Common Problems and Fixes

Dynamic array formulas can behave unexpectedly. Here are common issues:

• Spill range not empty – If there is data in the cells where the formula wants to spill, you get a #SPILL! error. Clear the obstructing cells.
• Headers not updating – If you use static headers, they won’t change when new categories appear. Use UNIQUE and TRANSPOSE for headers as shown above.
• Performance issues – Large datasets with many unique values can slow down calculations. Consider using a PivotTable for very large data.
• Excel vs. Google Sheets differences – TRANSPOSE works differently; in Google Sheets you can use TRANSPOSE(UNIQUE(...)) directly, but in Excel you might need to wrap with TOROW or TRANSPOSE depending on version.

Comparison: Dynamic Arrays vs. PivotTables

Both tools create two-way summaries, but they have different strengths:

• Dynamic arrays – Formulas are transparent, easy to audit, and update automatically when data changes. They are ideal for small to medium datasets and when you need the result to feed into other calculations.
• PivotTables – More powerful for large datasets, offer built-in grouping, filtering, and multiple aggregation types. However, they require manual refresh (unless you use macros) and are less flexible for direct cell references.

Choose dynamic arrays if you need a live, formula-driven summary; choose a PivotTable for heavy data exploration.

Frequently Asked Questions

Can I use dynamic arrays to create a two-way summary without UNIQUE?

Yes, you can manually list the categories, but then the summary won’t automatically include new categories. Using UNIQUE ensures the headers and row labels stay in sync with the source data.

Why does my formula return #SPILL!?

#SPILL! occurs when the output range overlaps existing data. Check the cells where the formula is trying to spill and clear them. Also ensure there are no merged cells in the spill area.

How do I get row and column totals in a dynamic array summary?

You can add totals by using SUM on the spilled rows and columns. For example, in Excel, =SUM(F2#) gives the grand total, and =SUM(F2:F10) gives a row total. For column totals, use =SUM(F2#) with TRANSPOSE if needed.

Does this work in Google Sheets?

Yes, but the syntax differs. Google Sheets supports UNIQUE, TRANSPOSE, and SUMIFS (though SUMIFS is available in newer versions). Alternatively, use QUERY for a more direct approach.

Can I use multiple aggregation types in one summary?

Yes, you can create multiple dynamic array formulas, each with a different aggregation function, and place them side by side. In Excel, you can also use HSTACK to combine them horizontally.

What if my data has blank cells?

Blank cells in the category fields are ignored by UNIQUE (it returns a blank), which can create an unwanted row/column. To exclude blanks, wrap the source range with FILTER to remove empty rows before applying UNIQUE.

Related

• COD Modern Warfare 4: Unlock All Operators
• Space-Grade Parts vs COTS: When Is Commercial Enough?
• Use Siri to Start Workout: Quick Guide

in t | Finish time | min | 50 | 0
in d | Distance | km | 10 | 0
out pace | Average pace | min/km | t / d | 2
out speed | Average speed | km/h | d / (t / 60) | 2