# Overcomplicating a simple problem

#### storm79

##### New Member
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?

### Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

#### SteveO59L

##### Well-known Member
Try a Pivot Table

#### goblin

##### Active Member
Pivot table is what you are looking for. Put the date as row data and the description as column data. Then drop the description field into the Data section as well.

That will give you exactly the table you are looking for.

#### storm79

##### New Member
Thanks for the replies. That's exactly what I needed.

I couldn't get it working at first, but the problem is with my data rather than the pivot table.

You see, I'm pulling the "date" information from a different workbook (basically by saying Cell A5 = Cell A5 in a different workbook). However, the data in the other workbook also contains time information in the same cell (01/01/07 15:34).

Even though I've got the date column formated to just display the date the pivot table is still using the time information and so the the result isn't quite right.

Is there an easy way to either get the pivot table to ignore the time information, or not to copy it across in the first place?

#### Norie

##### Well-known Member
Try INT.

=INT([Book2]Sheet1!A5)

Replies
8
Views
82
Replies
10
Views
82
Replies
13
Views
303
Replies
9
Views
212
Replies
11
Views
354

1,191,687
Messages
5,988,019
Members
440,125
Latest member
vincentchu2369

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back