See this:

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

In the meantime, try this: (normally entered, not an array)

=SUMIF(Trades!J2:J170,">="&M23,Trades!L2:L170)-SUMIF(Trades!J2:J170,">"&N23,Trades!L2:L170)

Biff

"Dave" <Dave@discussions.microsoft.com> wrote in message
news:8968D79F-BAE3-4963-804D-CBAD01C6C654@microsoft.com...
> Hello,
>
> I have this array formula:
> =SUM(IF(Trades!$J$2:Trades!$J$170>=$M$23,IF(Trades!$J$2:Trades!$J$170<=$N$23,Trades!$L$2:Trades!$L$170,0),0))
>
> I need the Trades!$J$2:Trades!$J$170, Trades!$J$2:Trades!$J$170 and
> Trades!$L$2:Trades!$L$170 to be dynamic in lenght.
>
> This is a stock trading sheet that I use to record my buys and sells of
> stock. As I buy and sell more stocks I would like the formula to adjust to
> include the new stocks. This formula is calculating the profit/loss for
> each
> week of each month. It works great right now except I have to go and
> change
> the lenght of the range 52 times (each week of the year) every time I sell
> more stocks.
>
> Is there an easy way to adjust the lenghts in this array so that I could
> change the lenght easier? is there a way to use a variable for the lenght
> of
> the array formula? Or an easier way to change the lenght for each week of
> the
> year?
>