56

I have a list of dates, each date in it can occur more than once. I want to count the number of times each date occurs (histogram) and display it in a graph (with the Y axis being the number of times the date occurs and the X axis being the date itself)?

Sample list:

19/05/2012
19/05/2012
19/05/2012
17/05/2012
17/05/2012
16/05/2012
16/05/2012
16/05/2012
16/05/2012
15/05/2012
15/05/2012
15/05/2012
15/05/2012
12/05/2012
12/05/2012
12/05/2012
7/05/2012

I clicked every menu option over the past few years and never did I find anything specific to this. Would this be a case for using PivotTables somehow?

AllInOne
  • 1,430
  • 2
  • 13
  • 32
SystemX17
  • 3,219
  • 4
  • 23
  • 35
  • 5
    The "minimal understanding of the problem being solved" part is rubbish. I was 2 menu options away from a solution. Sorry I must have to be 1 menu option off to be correct. Here is what I tried.. I clicked every menu option over the past few years and never did I find anything specific to this. The answer was that I didn't understand what a Pivot table was so I didn't even know to ask about it that SPECIFIC function. But again, congratulations for closing questions to stuff up the site. You are doing a great job and stackoverflow's quality and manners of people is getting worse - cheers! – SystemX17 Jun 19 '14 at 02:39
  • 6
    I'm surprised this wasn't moved to superuser rather that to be closed outright. – horta Sep 05 '14 at 20:42
  • 4
    It would've been quite sufficient to move it to Superuser. The user demonstrated an understanding of the problem being solved; just because they didn't document attempted solutions doesn't mean they don't understand the problem. Microsoft UIs are infamously non-intuitive, and the answers posted showed how close the user actually was to a solution. To all those who voted for closure, please collect your failboat boarding pass at the next kiosk. – Doktor J Dec 24 '14 at 18:54
  • 4
    Marked as off topic but 59205 views still later it's helping people.. can I please get this no longer marked as off topic. – SystemX17 Sep 20 '15 at 15:49

2 Answers2

75

The simplest is to do a PivotChart. Select your array of dates (with a header) and create a new Pivot Chart (Insert / PivotChart / Ok) Then on the field list window, drag and drop the date column in the Axis list first and then in the value list first.

Step 1:

Step1

Step 2:

Step2

edeboursetty
  • 5,344
  • 1
  • 31
  • 59
  • This gets me a count of the dates. I am unsure how to put it into a chart to show Y axis as stated above. – SystemX17 Jul 30 '12 at 11:38
  • The pivot table gives you a count by date, the pivot chart gives you a chart of that. Otherwise you can do a scatter plot chart using the data in the pivot table. – edeboursetty Jul 30 '12 at 11:42
  • Just chucked the results into a chart automatically and got the result I wanted. Thanks. – SystemX17 Jul 30 '12 at 11:51
  • 5
    I have found that to add date's count to values I need to right-click "DATE" in "fields to add" and select from new short-menu "Add to Values". This automatically adds count of rows. Probably newbie finding but not all may had to do this before. –  May 27 '14 at 07:42
  • oh man... I didn't think to drag the same item to two different sections! Thank you! I've been working on this for hours – Katie Feb 09 '16 at 23:40
  • damn, that was really easy. i was going in circles with this and cursing excel all the way – Lucas Pottersky Aug 16 '18 at 12:04
6

If you have Excel 2010 you can copy your data into another column, than select it and choose Data -> Remove Duplicates. You can then write =COUNTIF($A$1:$A$100,B1) next to it and copy the formula down. This assumes you have your values in range A1:A100 and the de-duplicated values are in column B.

martin
  • 2,300
  • 20
  • 27