Comparing Data across Years

[EliseFreedman]

I am trying to compare numbers of accidents from January 2004 till Now. My spreadsheet is in the format
08/01/2004 Slip,trip or fall January 2004
11/01/2004 Slip,trip or fall January 2004
13/01/2004 Slip,trip or fall January 2004
14/01/2004 Slip,trip or fall January 2004
14/01/2004 Slip,trip or fall January 2004
15/01/2004 Slip,trip or fall January 2004
16/01/2004 Slip,trip or fall January 2004
16/01/2004 Slip,trip or fall January 2004
18/01/2004 Slip,trip or fall January 2004
19/01/2004 Slip,trip or fall January 2004
20/01/2004 Slip,trip or fall January 2004
20/01/2004 Slip,trip or fall January 2004
26/01/2004 Slip,trip or fall January 2004

I would like to be able to create a chart comparing the levels of accidents in each month across the 3 years. For example January 04 v January 05 V January 06. However, no matter what I do, The chart comes up with all the 2004 months followed by all the 2005 months followed by all the 2006 months.What am I doing wrong. The MOnth and the Year are coming from a lookup table containing all the months and the dates for those months for example
Month Bucket
January 01/01/04-31/01/04
February01/02/04-29/02/04
March 01/03/04-29/03/04
April 01/04/04-30/04/06
June 01/06/04-30/06/04
July 01/07/04-31/07/04

Any help would be greatfully appreciated. I have been struggling with this all afternoon and it is really beginning to drive me round the twist.

Thanks in advance

Elise

 

[xlhelp]

Doing the easy version. If your years are in Columns and months are in Rows. Plot your chart. Right click on it; choose Source Data; under Data Range tab, select Rows; voila.


Stupidity has no handicap

 

[SkipVought]

Hi,

1. You could use the PivotTable wizard to summarize the data by Date -- GROUP & OUTLINE the Date using Month & Year.

2. You could use Years across and Month names down and use a formula like this to summarize the COUNTS.

I used the following NAMED RANGES for your 4 columns...
[tt]
IncidentDate
IncidentType
IncidentMonth
IncidentYear
[/tt]
Then using those ranges on a new sheet where years across columns and months on rows...
[tt]
=SUMPRODUCT((IncidentYear=B$1)*(IncidentMonth=$A2))
[/tt]

Skip,

_{[red]Be Advised![/red] The only distinction between a bird with one wing and a bird with two, is merely...
a difference of A Pinion!}