How To Sum Dynamic Array Function Spill Ranges

By Chris Newman •  Updated: 09/11/23 •  2 min read
How to create multiline text in ribbon tooltips with Excel.

Different Ways to Add Together Dynamic Array Formulas

If you are trying to figure out how to apply the addition operator in conjunction with Dynamic Array formulas in your Excel spreadsheet, you’ve come to the right place. In this article, we will explore a variety of ways you can sum up these dynamic ranges so that you ensure your data is always getting totaled up correctly.

Make sure you are referencing the Dynamic Array formula’s Spill Range correctly by pointing to the first cell in the range and using the hash (#) sign after the cell reference.

Subtotal A Single Spill Range Reference

If you would like to provide a subtotal for a specific Spill Range, simply use the SUM function. You will want to place your subtotals above your dynamic array formulas to ensure you don’t get a #SPILL! error if the formula’s output expands downward.

Example Excel Formula:


Screentip and Supertip example in Excel.

Add Multiple Spill Ranges Together Across Columns

If you are looking to output another dynamic range that sums Spilled Ranges across columns, you will need to forgo using the SUM function and simply utilize your addition operator.

Example Excel Formula:

=C5# + D5# + E5#

A super tip text example in Excel's Ribbon.

Provide A Grand Total for Multiple Spill Ranges

If you want to sum multiple Spilled Ranges into a single cell to serve as a grand total, you will want to reference each Spill Range within a SUM Function.

Example Excel Formula:

=SUM(C5#, D5#, E5#)

Grand Total Spill Range

You can also add together a range of consecutive Spill Ranges by referencing the first and last array ranges in your data set.

Example Formula:

=SUM(C5# : E5#)

Grand Total Spill Range Dynamic Arrays

I Hope This Microsoft Excel Article Helped!

Hopefully, I was able to explain how you can use addition in a variety of ways to sum together outputs from Dynamic Array Formulas. If you have any questions about this technique or suggestions on how to improve it, please let me know in the comments section below.

Keep Learning

Chris Newman

Chris Newman

Chris is a finance professional and Excel MVP recognized by Microsoft since 2016. With his expertise, he founded TheSpreadsheetGuru blog to help fellow Excel users, where he shares his vast creative solutions & expertise. In addition, he has developed over 7 widely-used Excel Add-ins that have been embraced by individuals and companies worldwide.

[FREE Training] 10 Amazing Excel Efficiency Tricks. Why Don't People Know These?!