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$1 70,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?

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" wrote in message
...
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$1 70,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?

Hi


Assuming your table on Trades sheet doesn't have any gaps, it has row 1 as
header row, and column A is always filled when there are some data in row:
Define named ranges like
Name1=OFFSET(Trades!$J$1,1,,COUNTA(Trades!$A:$A)-1,1)
Name2=OFFSET(Trades!$L$1,1,,COUNTA(Trades!$A:$A)-1,1)

My advice is to use SUMPRODUCT instead of array formula
=SUMPRODUCT(--(Name1=$M$23,--(Name1=$N$23,Name2)

Arvi Laanemets


"Dave" wrote in message
...
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?