I have a column that contains cells j3 through j27. In
this column I will enter 2004, or 2005, or 2006, or 2007
(these represent this year and the next 3 years) in
various cells, not in any particular order, but just
sporadically, and by entering the year it will trigger the
spreadsheet to sell stock and calculate gains and some
other stuff. Then another column that contains cells k3
through k27 will calculate the total capital gain that the
sale triggers for that particular transaction.

I would like to create 4 new fields, one called "gains in
2004", another called "gains in 2005", "gains in 2006",
and "gains in 2007". I would the "gains in 2004" cell to
identify all the cells in column j that say "2004", and
then sum up the gain that column K calculates for that
year. But it will need to exclude sales in 2005, 2006,
2007.
I guess if I could say the formula in plain english it
would say this:
if any cell in J3 through J27 says "2004", then look up
the cell that is right beside it(being the corresponding K
column cell), and then sum up the values in the K column
that correspond with a sale in 2004.

I think it's over my head but maybe someone knows a way.
Thanks alot

PS, at this point I am accomplishing this by taking up 4
entire columns(one for each year), using an if statement
that says =if(j3=2004,k3,0) and the I ran that down the
2004 column, then the same for each year's column. Then I
just summed up the column to get a total estimated gain
for each year. But if I can do it all in one cell for
each year that would certainly be more compact.

you might explore Sumif or SumProduct formulas
-----Original Message-----
I have a column that contains cells j3 through j27. In
this column I will enter 2004, or 2005, or 2006, or 2007
(these represent this year and the next 3 years) in
various cells, not in any particular order, but just
sporadically, and by entering the year it will trigger
the
spreadsheet to sell stock and calculate gains and some
other stuff. Then another column that contains cells k3
through k27 will calculate the total capital gain that
the
sale triggers for that particular transaction.

I would like to create 4 new fields, one called "gains in
2004", another called "gains in 2005", "gains in 2006",
and "gains in 2007". I would the "gains in 2004" cell to
identify all the cells in column j that say "2004", and
then sum up the gain that column K calculates for that
year. But it will need to exclude sales in 2005, 2006,
2007.
I guess if I could say the formula in plain english it
would say this:
if any cell in J3 through J27 says "2004", then look up
the cell that is right beside it(being the corresponding
K
column cell), and then sum up the values in the K column
that correspond with a sale in 2004.

I think it's over my head but maybe someone knows a way.
Thanks alot

PS, at this point I am accomplishing this by taking up 4
entire columns(one for each year), using an if statement
that says =if(j3=2004,k3,0) and the I ran that down the
2004 column, then the same for each year's column. Then
I
just summed up the column to get a total estimated gain
for each year. But if I can do it all in one cell for
each year that would certainly be more compact.


.