In , Sarah H.
wrote:

In , Claus Busch
wrote:

=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.

This also works:

=SUMPRODUCT(SUBTOTAL(109,INDIRECT("q" & ROW(3:44442))),*(TblPriorYrs[Managed]<""))
or
=SUMPRODUCT(SUBTOTAL(109,INDIRECT("q" & ROW(3:44442))),--(TblPriorYrs[Managed]<""))

Still trying to find another way around the ugly INDICRECT
statement. I can't even set a named value as equal to 44442 and
have it work. Ugh.

Much obliged, Claus.

/sh