make chart from dates in a column
 

I cant seem to figure this out. I have a worksheet that has a column
of dates. I want to create a chart that shows the sum of all the items
broken out by month and year. How can I do this?

Example:

10/10/2007 16:04
10/17/2007 16:10
1/3/2008 10:32
1/5/2008 14:00
2/4/2008 11:00
2/4/2008 11:30
2/10/2008 15:00

I would like this output to create a chart from -
Oct 2007 = 2
Jan 2008 = 2
Feb 2008 = 3

TIA

make chart from dates in a column
 

=SUMPRODUCT(--(MONTH(A1:A7)=10),--(YEAR(A1:A7)=2007))

"coastergeez" wrote:

I cant seem to figure this out. I have a worksheet that has a column
of dates. I want to create a chart that shows the sum of all the items
broken out by month and year. How can I do this?

Example:

10/10/2007 16:04
10/17/2007 16:10
1/3/2008 10:32
1/5/2008 14:00
2/4/2008 11:00
2/4/2008 11:30
2/10/2008 15:00

I would like this output to create a chart from -
Oct 2007 = 2
Jan 2008 = 2
Feb 2008 = 3

TIA

make chart from dates in a column
 

With your supplied data in A1:B7 try:

="Oct 2007 = "&SUMPRODUCT((A1:A7<"")*(MONTH(A1:A7)=10)*(YEAR(A 1:A7)=2007))

or:

=TEXT(A1,"mmm yyyy")&" =
"&SUMPRODUCT((A1:A7<"")*(MONTH(A1:A7)=10)*(YEAR(A 1:A7)=2007))

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"coastergeez" wrote in message
...
I cant seem to figure this out. I have a worksheet that has a column
of dates. I want to create a chart that shows the sum of all the items
broken out by month and year. How can I do this?

Example:

10/10/2007 16:04
10/17/2007 16:10
1/3/2008 10:32
1/5/2008 14:00
2/4/2008 11:00
2/4/2008 11:30
2/10/2008 15:00

I would like this output to create a chart from -
Oct 2007 = 2
Jan 2008 = 2
Feb 2008 = 3

TIA

make chart from dates in a column
 

On Apr 9, 8:15*am, Teethless mama
wrote:
=SUMPRODUCT(--(MONTH(A1:A7)=10),--(YEAR(A1:A7)=2007))



That worked great! Thanks! What if in another column I had states. And
I wanted to see the number sorted by a state and month.

So column A1 would have and B1 would have
10/10/2007 16:04 CA
10/17/2007 16:10 OR
1/3/2008 10:32 MA
1/5/2008 14:00 MA
2/4/2008 11:00 OR
2/4/2008 11:30 MA
2/10/2008 15:00 OR

then I could make a row by states and column months
01/2008 02/2008
MA 2 1
OR 0 2

Does that makes sense? I currently use the formula to sum them all up
by state using COUNTIF(state!B1:B2000,"CA")
Can I combine the COUNTIF with the SUMPRODUCT in one formula?

Thanks so much!

make chart from dates in a column
 

Teethless mama dosen't seem to be around at the moment. Just add the new
condition to the SUMPRODUCT():

=SUMPRODUCT(--(MONTH(A1:A7)=10),--(YEAR(A1:A7)=2007),--(C1:C7="CA"))

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"coastergeez" wrote in message
...
On Apr 9, 8:15 am, Teethless mama
wrote:
=SUMPRODUCT(--(MONTH(A1:A7)=10),--(YEAR(A1:A7)=2007))



That worked great! Thanks! What if in another column I had states. And
I wanted to see the number sorted by a state and month.

So column A1 would have and B1 would have
10/10/2007 16:04 CA
10/17/2007 16:10 OR
1/3/2008 10:32 MA
1/5/2008 14:00 MA
2/4/2008 11:00 OR
2/4/2008 11:30 MA
2/10/2008 15:00 OR

then I could make a row by states and column months
01/2008 02/2008
MA 2 1
OR 0 2

Does that makes sense? I currently use the formula to sum them all up
by state using COUNTIF(state!B1:B2000,"CA")
Can I combine the COUNTIF with the SUMPRODUCT in one formula?

Thanks so much!