Sign up for our Excel webinar, times added weekly: https://www.excelcampus.com/blueprint-registration/
In this video I explain how to create total rows and columns in the spill ranges of dynamic array formulas. Download the Excel file: https://www.excelcampus.com/functions/total-rows-dynamic-arrays/

This was a great question from Thomas, a member of our Elevate Excel Training Program. He wanted to know how to add floating totals to spill ranges, since they can frequently change size (number of rows/columns)? 🤷‍️

Adding the total row to a spill range is not directly possible in Excel, so this solution is a bit of a workaround. It just uses a few dynamic array formulas and conditional formatting.

You will learn how to use the FILTER, SEQUENCE, ROWS, and SORTBY functions

The technique can be used to create multiple total rows or columns that calculate various metrics like sum, average, count, etc. You can even use this technique to create subtotal rows within the spill range.

Please leave a comment below with any questions or suggestions. Thanks!

Related Videos & Resources:
- Dynamic Array Formulas & Spill Ranges Explained: https://youtu.be/1HF0UGMF070
- Conditional Formatting Based on Another Cell's Value: https://youtu.be/YAzK9vHa178
- UNIQUE and FILTER Functions Explained: https://youtu.be/ftDXUOKCaUM
- Drop-down List Icon Always Visible: https://www.excelcampus.com/tips/drop-list-arrow-always-visible/
00:00 Introduction
00:59 Dynamic Array Formulas
05:43 Conditional Formatting
09:22 Final Notes