Explanation
At the core, this formula uses INDEX to create an expanding reference like this:
INDEX([Color],1):[@Color] // expanding range
On the left side of the colon (:), the INDEX function returns a reference to the first cell in the column.
INDEX([Color],1) // first cell in color
This works because, the INDEX function returns a reference to the first cell, not the actual value. On the right side of the colon, we get a reference to the current row of the color column like this:
[@Color] // current row of Color
This is the standard structured reference syntax for “this row”. Joined with the colon, these two references create a range that expands as the formula is copied down the table. So, we swap these references into the SUM function, we have:
SUM(--(B5:B5=[@Color])) // first row
SUM(--(B5:B11=[@Color])) // last row
Each of the expressions above generates an array of TRUE/FALSE values, and the double negative (–) is used to convert these values to 1s and 0s . So, in the last row, we end up with:
SUM({0;0;0;1;0;0;0;0;1;0;1}) // returns 3
The rest of the formula simply concatenates the color from the current row to the count returned by SUM:
=[@Color]&" - "&3
="Gold"&" - "&3
="Gold - 3"
Simple expanding range?
Why not use a simple expanding range like this?
SUM(--($B$5:B5=[@Color]))
For some reason, this kind of mixed reference becomes corrupted in an Excel Table as rows are added. Using INDEX with a structured reference solves the problem.
Explanation
At the core, this formula has a simple pattern like this:
=SUM(first:current)
Where “first” is the first cell in the Total column, and “current” is a reference to a cell in the current row of the Total column.
To get the a reference to the first cell, we use INDEX like this:
INDEX([Total],1)
Here, the array is the entire “Total” column and row number is 1. This works because, the INDEX function returns a reference to the first cell, not the actual value.
To get a reference to the current row, we use:
[@Total]
This is the standard structured reference syntax for “this row”.
As the formula is copied down the column, the reference to the first cell doesn’t change, but the referent to the current cell changes at each row. The result is a reference that expands. The SUM function sums the values in the range at each row, creating a running total.
Simple expanding range
Why not use a simple expanding range like this?
=SUM($E$5:E5)
For some reason, this kind of mixed reference becomes corrupted in an Excel Table as rows are added. Using INDEX with a structured reference solves the problem.
