Menu
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?
393
Use the COUNTIF function to count how many times a particular value appears in a range of cells.
In C1 , paste this formula: =COUNTIF(A:A;B1) (Use a ; in Excel 2010+, otherwise use a , ). In the bottom right corner of C1 , click the black square and drag it down until you've reached the bottom of column B. Finally, create a graph as you normally would.
Count number of occurrences in a date range with formulas Select a blank cell that you want to place the count result, and enter this formula =SUMPRODUCT((A2:A14>=$D$1)*(A2:A14<=$D$2)), press Enter key. Now the number of occurrence in date range has been counted.
In the chart, right-click the category axis, and then click Format Axis. In the Format Axis pane, select the Axis Options tab. Expand Axis Options, and then under Axis Type, make sure Date axis is selected. Under Units, next to Base, select Days, Months, or Years.
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:
Step 2:
179
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
