There are several ways to do this, one way without using a volatile function
would be

=SUMPRODUCT(--($A$9:INDEX($A$9:$A$20000,COUNTA($A$9:$A$20000))))

which would be the same as

=SUMPRODUCT(--($A$9:$A$50))

now add 5 values

and it will adapt, you can also define a dynamic named range like here

http://www.contextures.com/xlNames01.html#Dynamic

that particular website is the best IMHO if you want to learn excel

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

Portland, Oregon




"qwopzxnm" wrote in
message ...

Hello-

Is there a way to set up a SUMPRODUCT so that it will automatically
adjust the array length to the last value in a column???

For example if I have column A data that starts in row 9 and ends in
row 50. Part of my SUMPRODUCT would look like --(A9-A50),

Then if I add 5 rows of data could I set up a SUMPRODUCT to
automatically adjust to now include those extra 5 rows?


--
qwopzxnm
------------------------------------------------------------------------
qwopzxnm's Profile:
http://www.excelforum.com/member.php...o&userid=27557
View this thread: http://www.excelforum.com/showthread...hreadid=513842