Where data is an Excel Table based on the data in B5:D17. The result from SUMIF spills into the range G5:I9, and the results show the sum of Qty for each City and Size combination. Note: Dynamic Array Formulas are only available in Excel 365 and Excel 2021. 

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: 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: 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: The UNIQUE function returns a vertical array like this: And the TRANSPOSE function converts this array into a horizontal array like this: 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: 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: The other arguments are range/criteria pairs. The first pair targets cities: The second range/criteria pair targets 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: 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.

Dave Bruns

Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.