How To Sum Dynamic Array Function Spill Ranges

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:

=SUM(C5#)

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#

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#)

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#)

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.

Format Excel Charts Like a Pro!!!

Learn my best Excel chart design hacks. These will turn your ugly charts into professionally crafted graphics, leaving your boss speechless!!!

Get These Hacks Now For Free!

Keep Learning

Explore more →

7 Ways To Generate Unique Values List In Excel

There are many scenarios you may come across while working in Excel where you only desire unique values in a...

How To Use A Double XLOOKUP Formula

Have you heard of an XLOOKUP/XLOOKUP? No, I didn’t stutter in my writing….there is now such a thing as a...

Which Excel Versions Have XLOOKUP Function? [Solved]

Versions Of Microsoft Excel For XLOOKUP Function The following versions of Microsoft Excel include the XLOOKUP function: Excel 2021 or...

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.