array formula with a dynamic range.
 

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?

array formula with a dynamic range.
 

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?

array formula with a dynamic range.
 

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?