I have a column with dates in it (Jan-12-2004) I have another column with the
transaction price in it like $21.95.

I need to sum the column by month. I can do mid(Jan-12-2004,1,3) to take out
the info except Jan but then how do you sum from that point on.

You could use

=SUMPRODUCT(--(MONTH(A1:A100)=1),B1:B100)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"jnorton" wrote in message
...
I have a column with dates in it (Jan-12-2004) I have another column with
the
transaction price in it like $21.95.

I need to sum the column by month. I can do mid(Jan-12-2004,1,3) to take
out
the info except Jan but then how do you sum from that point on.

let me add a sample

Sheet 1
A B
Dec-14-2004 40.56
Dec-14-2004 119.97
Dec-14-2004 120.54
Dec-15-2004 -33.42
Dec-15-2004 121.04
Dec-15-2004 -40.36
Dec-16-2004 32.83
Dec-16-2004 32.79
Dec-16-2004 39.66
Dec-20-2004 32.13
Dec-20-2005 119.52
Dec-20-2006 119.04
Dec-20-2007 -39.48
Dec-20-2007 119.04
Dec-21-2005 120.95
Dec-22-2004 120.39
Dec-22-2004 -39.75
Dec-22-2004 33.05
Dec-23-2004 120.5
Dec-23-2004 -32.86
Dec-27-2004 32.81
Dec-27-2004 39.8
Dec-27-2004 121.74
Dec-27-2004 121.49
Dec-28-2004 -32.7
Dec-28-2004 39.65
Dec-28-2004 33.29
Dec-28-2004 -40.14
Jan-03-2005 38.85
Jan-03-2005 31.89
Jan-03-2005 119.2
Jan-03-2005 116.66
Jan-04-2005 38.74
Jan-05-2005 38.58
Jan-05-2005 31.34
Jan-05-2005 118.89
Jan-06-2005 38.76
Jan-06-2005 116.55
Jan-06-2005 119.06
Jan-06-2005 31.78
Jan-07-2005 38.7
Jan-07-2005 118.59
Jan-07-2005 31.57
Jan-07-2005 116.19
Jan-07-2005 119.11
Jan-07-2005 117.04
Jan-10-2005 31.07
Jan-10-2005 38.33
Jan-10-2005 118.34
Jan-10-2005 116.46
Jan-11-2005 31.2
Jan-11-2005 115.92

Sheet2
A B
Dec Jan
X y

I need only the positive values for X and Y from sheet one and the sum needs
to be placed in the corrosponing month colum on sheet two
"Bob Phillips" wrote:

You could use

=SUMPRODUCT(--(MONTH(A1:A100)=1),B1:B100)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"jnorton" wrote in message
...
I have a column with dates in it (Jan-12-2004) I have another column with
the
transaction price in it like $21.95.

I need to sum the column by month. I can do mid(Jan-12-2004,1,3) to take
out
the info except Jan but then how do you sum from that point on.

=SUMPRODUCT(--(TEXT(Sheet1!A1:A100,"mmm")=A1),--(Sheet1!B1:B1000),Sheet1!B1
:B100)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"jnorton" wrote in message
...
let me add a sample

Sheet 1
A B
Dec-14-2004 40.56
Dec-14-2004 119.97
Dec-14-2004 120.54
Dec-15-2004 -33.42
Dec-15-2004 121.04
Dec-15-2004 -40.36
Dec-16-2004 32.83
Dec-16-2004 32.79
Dec-16-2004 39.66
Dec-20-2004 32.13
Dec-20-2005 119.52
Dec-20-2006 119.04
Dec-20-2007 -39.48
Dec-20-2007 119.04
Dec-21-2005 120.95
Dec-22-2004 120.39
Dec-22-2004 -39.75
Dec-22-2004 33.05
Dec-23-2004 120.5
Dec-23-2004 -32.86
Dec-27-2004 32.81
Dec-27-2004 39.8
Dec-27-2004 121.74
Dec-27-2004 121.49
Dec-28-2004 -32.7
Dec-28-2004 39.65
Dec-28-2004 33.29
Dec-28-2004 -40.14
Jan-03-2005 38.85
Jan-03-2005 31.89
Jan-03-2005 119.2
Jan-03-2005 116.66
Jan-04-2005 38.74
Jan-05-2005 38.58
Jan-05-2005 31.34
Jan-05-2005 118.89
Jan-06-2005 38.76
Jan-06-2005 116.55
Jan-06-2005 119.06
Jan-06-2005 31.78
Jan-07-2005 38.7
Jan-07-2005 118.59
Jan-07-2005 31.57
Jan-07-2005 116.19
Jan-07-2005 119.11
Jan-07-2005 117.04
Jan-10-2005 31.07
Jan-10-2005 38.33
Jan-10-2005 118.34
Jan-10-2005 116.46
Jan-11-2005 31.2
Jan-11-2005 115.92

Sheet2
A B
Dec Jan
X y

I need only the positive values for X and Y from sheet one and the sum
needs
to be placed in the corrosponing month colum on sheet two
"Bob Phillips" wrote:

You could use

=SUMPRODUCT(--(MONTH(A1:A100)=1),B1:B100)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"jnorton" wrote in message
...
I have a column with dates in it (Jan-12-2004) I have another column
with
the
transaction price in it like $21.95.

I need to sum the column by month. I can do mid(Jan-12-2004,1,3) to
take
out
the info except Jan but then how do you sum from that point on.