In , Claus Busch
wrote:

Am Thu, 8 Jan 2015 15:04:52 +0000 (UTC) schrieb Sarah H.:

=SUMPRODUCT(SUBTOTAL(103,Table1[Managed]),SUBTOTAL(109,Table1[PnlRealized]))

try:
=SUMPRODUCT(SUBTOTAL(9,INDIRECT("Q"&ROW(22:1000))) *(S22:S1000<""))

Good, thank you, that works:
=SUMPRODUCT(SUBTOTAL(9,INDIRECT("q" & ROW(3:100000)))*(S3:S100000<""))

(There are 44,442 rows of data presently, so 1000 was too small.) :-)

Now please tell my why that works.

=ROW(3:100000) seems to be a horizontal area. I can't figure out
what you've done here. I do understand the INDIRECT function generally.

Also, what's the point of using a newfangled table (that presets
its data range nicely) if we just have to resort to old-fashioned
hard-coded (and "brittle") ranges with column letters? :-)

Much obliged, Claus.

/sh