Running count in Table

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.