Dynamic two-way sum

In this example, the goal is to create a formula that performs a dynamic two-way sum of all City and Size combinations in the range B5:D17 . The solution shown requires four basic steps:

1. Create an Excel Table called data
2. List unique cities with the UNIQUE function
3. List unique sizes with the UNIQUE function
4. Generate sums with the SUMIFS function

Create the Excel Table

One of the key features of an Excel Table is its ability to dynamically resize when rows are added or removed. In this case, all we need to do is create a new table named data with the data shown in B5:D17.  You can use the keyboard shortcut Control + T.

Video: How to create an Excel table

The table will now automatically expand or contract as needed.

List unique cities

The next step is to list the unique cities in the "City" column starting in cell F5. For this we use the UNIQUE function. The formula in F5 is:

=UNIQUE(data[City]) // unique city names

The result from UNIQUE is a spill range starting in cell F5 listing all of the unique city names in the City column of the table. This is what makes this solution fully dynamic. The UNIQUE function will continue to return a list of unique cities, even as data in the table changes. 

Video: Intro to the UNIQUE function

List unique sizes

Two perform a two-way sum, we also need a list of unique sizes starting in cell G4. We can do this with a similar formula:

UNIQUE(data[Size]) // unique sizes

However, unlike cities, we need the list of sizes to run horizontally across above the sums. To change the output from vertical to horizontal, we nest the UNIQUE formula in the TRANSPOSE function. The final formula in G4 is:

=TRANSPOSE(UNIQUE(data[Size])) // horizontal array

The UNIQUE function returns a vertical array like this:

{"L";"M";"S"}

And the TRANSPOSE function converts this array into a horizontal array like this:

{"L","M","S"}

Note the comma instead of a semicolon in the second array. The UNIQUE function will continue to return a list of unique sizes, even if data in the table changes and sizes are added or removed.

Video: What is an array?

Generate the sums

We now have what we need to calculate the sums. Because we have both unique cities and unique sizes on the worksheet as spill ranges, we can use the SUMIFS function for this task. The formula in G5 is:

=SUMIFS(data[Qty],data[City],F5#,data[Size],G4#)

The first argument in SUMIFS is sum_range. This is the range that contains numbers to sum. In this example, this is the Qty column in the table:

data[Qty] // sum_range

The other arguments are range/criteria pairs. The first pair targets cities:

data[City],F5# // all cities, unique cities

The second range/criteria pair targets sizes:

data[Size],G4# // all sizes, unique sizes

When data changes

The key advantage to this formula approach is that it responds instantly to changes in the data. If new rows are added that refer to existing cities and sizes, the spill range remains the same size, and SUMIFS simply returns an updated set of sums. If new rows are added that include new cities and/or new sizes, these are captured by the UNIQUE function, which expands the spill ranges in F5 and G4 as needed. Likewise, if rows are deleted from the table, spill ranges are reduced by UNIQUE as needed. In all cases, the spill ranges represent the current list of unique cities and sizes, and the SUMIFS function returns a current set of sums.

Legacy Excel workaround

Dynamic array formulas are new in Excel 365 and Excel 2021. In legacy versions of Excel that don't support dynamic array formulas, it is still possible to compute the sums in G5:I9 with the SUMIFS function. However, certain references must be carefully locked* so that the formula can be copied across and down:

=SUMIFS(data[[Qty]:[Qty]],data[[City]:[City]],$F5,data[[Size]:[Size]],G$4)

Since the UNIQUE function is not available in older versions of Excel, this formula requires that Cities in F5:F9 and Sizes in G4:I4 be created manually.

* This is a good illustration of a key benefit of dynamic array formulas: because there is just one formula, there is no need to use complicated mixed and absolute references. Dynamic array formulas are therefore easier to create and maintain.

Pivot Table option

A pivot table would also be a good way to solve this problem and would provide additional capabilities. However, one drawback is that pivot tables need to be refreshed to show the latest data. Formulas, on the other hand, update instantly when data changes.

Dynamic Array Training

If you need training for dynamic arrays in Excel, see our course: Dynamic Array Formulas.