One possible solution uses this weird range reference: So, we need a way to append total rows or columns to the bottom of the spill range, and/or include them inside the spill range. The Solution: The Overflowing Spill Range? You will also want to make sure you are on a version of Excel that has dynamic arrays. If you're not familiar with dynamic arrays yet, then checkout my post & video on Dynamic Array Formulas and Spill Ranges. Therefore, it's difficult to put a total row below the spill range because the last row of the spill range is constantly changing.

The number of rows in the spill range changes when a new selection is made from the Customer drop-down list. In the example above I'm using the FILTER function to create a report by customer. One common issue with dynamic arrays is that we can't append data to the bottom of the spill ranges yet. The spill ranges will typically change size (more or less rows or columns) as input values change. The Problem: Spill Ranges Change Sizeĭynamic array formulas are an awesome way to create interactive summary reports, and can be used as an alternative to pivot tables. I can't say it's easy, but I think the technique will be very useful in many different scenarios. The solution just uses a few Dynamic Array formulas and Conditional Formatting. However, I recently stumbled upon an interesting range reference that allows us to create a workaround solution. This is NOT directly possible in Excel yet. Thomas, a member of our Elevate Excel Training Program, asked a great question in the Community Forum about adding total rows or summary rows to spill ranges. Total-Rows-for-Dynamic-Arrays-VSTACK.xlsx Download Floating Totals for Spill Ranges?

Another word for running total