Challenge 3: Counting a Spill Range with # vs. a Fixed Range — Solution Walkthrough

Formula using the spill reference operator:
  =COUNTA(D2#)

This counts however many cells the D2 spill range CURRENTLY occupies
— today that's D2:F15, but the formula itself doesn't hard-code that
address anywhere; # always means "whatever the live spill range
currently is."

Why =COUNTA(D2:F15) (a fixed range) would eventually give a wrong
answer:

The specific circumstance where this breaks: if the underlying FILTER
formula in D2 ever matches MORE rows than it currently does — for
example, more transactions get added to the source data that satisfy
the filter condition — the live spill range would automatically grow
beyond row 15, say to D2:F20. The fixed-range formula COUNTA(D2:F15)
has no awareness of that growth at all; it keeps counting only
whatever sits within rows 2 through 15, permanently missing every row
of the spill range that now extends past row 15. The count it returns
would silently understate the true number of matching rows, with no
error or warning that anything was missed.

(If the spill range instead SHRANK — fewer matches than before — the
fixed-range version would happen to still return a technically
correct count, since COUNTA only counts non-empty cells and the extra
rows within D2:F15 would simply be empty. The real risk is entirely
in the range GROWING past the fixed boundary, not shrinking within it.)

Notes:
- This is the same underlying lesson as Excel Fundamentals Chapter
  7's plain-range-vs-Table comparison: a fixed address is a snapshot
  of a specific set of cells, while a reference built to track
  something live (a Table, or here, the # spill operator) keeps
  describing the current, correct scope automatically as the
  underlying data changes shape.