Dynamic Arrays 2

This is the second post in the Dynamic Arrays series. In the first post, we talked about how formulas can return multiple values, the resulting spill range, and two dynamic array functions. In this post, we’ll talk about how to refer to the spill range with other formulas.

Video

Spill Range

The spill range includes the cells that store the results of a dynamic array formula. In other words, when you write a dynamic array formula in a cell, for example into cell B8:

And then hit Enter, the results spill out of the formula cell into the cells below:

The range that contains all of the formula results, B8:B10 in this case, is known as the Spill Range.

Now, the cool part about the spill range, besides the fact that it is dynamic, is that we can reference it from other formulas.

Spill Reference

Let’s say we’d like to populate the Total column with the sum of the accounts. The data table, Table1, looks like this:

So, we figure we can use the SUMIFS function to generate the summary.

We write the following formula into C8:

=SUMIFS(Table1[Amount],Table1[Account],B8)

And press Enter:

It computed the correct result, so that is good. But, now we would need to manually fill that formula down to compute the totals for Meals and Phone.

But, if we know how to use the Spill Reference Operator #, then Excel will fill the formula down for us … and continue doing so as the size of the spill range changes.

So, we update our reference from B8 (individual cell) to B8# (entire spill range) in our formula, like this:

=SUMIFS(Table1[Amount],Table1[Account],B8#)

We hit Enter and bam:

It too spills down … nice!

And, the best part is that when our source data table has more rows and more accounts, the results will dynamically update.

If you are familiar with PivotTables, you’ll quickly realize we could build the example above using a PivotTable. The dynamic arrays approach is kinda like a cross between a traditional formula-based report and a PivotTable. It uses formulas, but has the dynamic element we love about PivotTables. So, depending on the context of your workbook, this may offer a nice option.

What do you think about this capability? I’d like to know … just post a quick comment below!

Sample File