I'm certain there's a simple solution to this problem if only I knew the correct function(s) to use!
I've got a spreadsheet with a list of events and the date they occured. There's simply two colums, Date and Description. The description can only be one of four possibilities, so say "Alpha", "Bravo", "Tango" or "Foxtrot", and the sheet looks a little like this:
01/01/2007 Alpha
02/01/2007 Alpha
02/01/2007 Bravo
02/01/2007 Bravo
02/01/2007 Foxtrot
03/01/2007 Tango
03/01/2007 Alpha
04/01/2007 Foxtrot
07/01/2007 Tango
07/01/2007 Alpha
What I need to do is count the number of events for each day. So for example I'd like the spreadsheet would give me a second column of data listing each unique day and the number of events, by type that occured then. E.G:
01/01/2007 Alpha x 1
02/01/2007 Alpha x 1, Bravo x 2, Foxtrot x 1
03/01/2007 Alpha x 1, Tango x 1
04/01/2007 Foxtrot x 1
05/01/2007 No data
06/01/2007 No data
07/01/2007 Alpha x 1, Tango x 1
I wouldn't need the "Alpha x 1", that's just for illustration, but if it could return a count in an appropriate column for the number of events that day then that would be ideal. So start at Row 5, in Cell A put the date, Cell B put the Alpha count, cell C put the Bravo count, etc. etc. then move to a new row for a new day.
I've tried various different ways to work this out by trying to combine the "Countif" function with other functions, but have failed. I'm sure I'm overcomplicating the method needed, but I'm really stuck.
Has anyone got any ideas?
I've got a spreadsheet with a list of events and the date they occured. There's simply two colums, Date and Description. The description can only be one of four possibilities, so say "Alpha", "Bravo", "Tango" or "Foxtrot", and the sheet looks a little like this:
01/01/2007 Alpha
02/01/2007 Alpha
02/01/2007 Bravo
02/01/2007 Bravo
02/01/2007 Foxtrot
03/01/2007 Tango
03/01/2007 Alpha
04/01/2007 Foxtrot
07/01/2007 Tango
07/01/2007 Alpha
What I need to do is count the number of events for each day. So for example I'd like the spreadsheet would give me a second column of data listing each unique day and the number of events, by type that occured then. E.G:
01/01/2007 Alpha x 1
02/01/2007 Alpha x 1, Bravo x 2, Foxtrot x 1
03/01/2007 Alpha x 1, Tango x 1
04/01/2007 Foxtrot x 1
05/01/2007 No data
06/01/2007 No data
07/01/2007 Alpha x 1, Tango x 1
I wouldn't need the "Alpha x 1", that's just for illustration, but if it could return a count in an appropriate column for the number of events that day then that would be ideal. So start at Row 5, in Cell A put the date, Cell B put the Alpha count, cell C put the Bravo count, etc. etc. then move to a new row for a new day.
I've tried various different ways to work this out by trying to combine the "Countif" function with other functions, but have failed. I'm sure I'm overcomplicating the method needed, but I'm really stuck.
Has anyone got any ideas?