Two-way summary with dynamic arrays

This worksheet contains several hundred rows of sample order data in anExcel Tablecalled “data”.

I’ll first build a pivot table to summarize this data by Color and Region.

I’ll keep the pivot table on the same sheet.

I’ll also set Total to display currency.

Finally, I’ll turn off Grand Totals, and disable Auto-Fit, to keep things from moving around.

As always, a pivot table like this is very quick and easy to build.

Now let’s make a similar table with dynamic array formulas.

First, I’ll use theUNIQUE functionto extract a list of colors.

To match the pivot table, I’ll also sort these values with theSORT function.

Next, I’ll do the same thing with Regions.

Here again, we want unique values that are sorted.

Now we’re ready to calculate totals and for this, we’ll use theSUMIFS function.

The sum range is Total.

Criteria range 1 is the color column, and the criteria is the dynamic array that contains unique colors.

Criteria range 2 is region, and the criteria is the dynamic array that contains regions.

When I enter the formula, SUMIFS calculates the subtotals and delivers all results in a single dynamic array.

I’ll format these values as currency, to match the pivot table.

And one advantage of the dynamic array version is that it responds instantly to any changes in the data.

In contrast, the Pivot Table must be manually refreshed.

On the other hand, pivot tables still have significant advantages.

Pivot tables maintain formatting, unlike dynamic arrays.

However, in cases where you want instantly updated information, dynamic array formulas are a great new option.