Shane Devenshire wrote...
....
now enter =SUM(INDIRECT("$L"&TEXT(ROW(),0)&":$L$"&TEXT($B$9, 0)))
not 2 anymore.

Why, I suppose you could say because Microsoft programmed it that way.
....

The functions which accept variable numbers of arguments with the
exceptions of CONCATENATE, CHOOSE and NPV *ALWAYS* return single
results because those functions' semantics *REQUIRE* them to return
single values. I call these the aggregating functions. Getting
technical, they produce scalar descriptive statistics from arbitrary
collections of data values.

NPV is the joker in the group. If its 1st argument is a single area
range or array, then NPV returns an array result with the same size/
shape as its 1st argument with the array result being the NPVs of the
subsequent arguments using each of the interest rates in the 1st
argument separately.

Anyway, ROW() always returns an array, and that often causes problems
in dynamic range constructs. Excel seems to treat such constructs as
arrays of range references. Speculation: the aggregating functions
handle them because they're built to handle arbitrary iteration both
over all of and within each of their arguments. Most other functions
are more delicate and choke on such argument values.

Maybe a different indicator would be functions which can handle
multiple area ranges (other than AREAS, CELL, CHOOSE and INDEX) can
handle arrays of range references.

I've found it much safer to use ROWS rather than ROW. For example, if
you want to use ROW() in cell X99, you could use ROWS($A$1:X99) or ROWS
(INDEX($1:$65536,1,1):X99) instead. The latter construct is immune to
row/column insertion/deletion other than deleting row 99 or column X,
which would blast the formula out of existence anyway. ROWS, unlike
ROW, always returns a scalar, i.e., a single, nonarray value.

IMO, it's unwise to use ROW except when you want to return multiple-
value array results.