Frequency Counter

[phester90]

Hello everyone thank you in advance for your help.

I am currently making a file that has a list of dates.
(ie 12-3-2011, 1-4-2012, 1-4-2012, 3-8-2012)

I want to count the frequency of the dates and then be able to plot them
on a graph. I have tried a countif function and a histogram.

The countif function messes up when there are two of the came dates. The histogram does not change when I enter new information into my dates. Also I often run into a problem because it plots the dates twice if they are the same. (ie for 1-4-2012 it plots the date and 2. Then for the second 1-4-2012 it plots the date and 2. I only need the same date once. Can excel ignore the duplicate date while still counting it for frequency)

-Phester90

 

[Robert Mika]

Extracy unique item and them use COUNTIF:

Excel 2010<table rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB" cellpadding="2.5px"><colgroup><col style="background-color: #DAE7F5" width="25px"><col><col><col></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr><td style="color: #161120;text-align: center;">1</td><td style=";">List</td><td style=";">Unique</td><td style=";">Count</td></tr><tr><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">12/3/2011</td><td style="text-align: right;;">12/3/2011</td><td style="text-align: right;;">1</td></tr><tr><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">1/4/2012</td><td style="text-align: right;;">1/4/2012</td><td style="text-align: right;;">2</td></tr><tr><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">1/4/2012</td><td style="text-align: right;;">3/8/2012</td><td style="text-align: right;;">1</td></tr><tr><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">3/8/2012</td><td style="text-align: right;;"></td><td style=";"></td></tr><tr><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";"></td></tr><tr><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";"></td></tr><tr><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";"></td></tr><tr><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";"></td></tr><tr><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";"></td></tr></tbody></table>

Sheet1​

<table rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" cellpadding="2.5px" width="85%"><tbody><tr><td style="padding:6px">Worksheet Formulas<table rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB" cellpadding="2.5px" width="100%"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th style=" background-color: #DAE7F5;color: #161120" width="10px">C2</th><td style="text-align:left">=COUNTIF($A$2:$A$17,B2)</td></tr></tbody></table></td></tr></tbody></table>
<table rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" cellpadding="2.5px" width="85%"><tbody><tr><td style="padding:6px">Array Formulas<table rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB" cellpadding="2.5px" width="100%"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th style=" background-color: #DAE7F5;color: #161120" width="10px">B2</th><td style="text-align:left">{=INDEX(list,MATCH(0,COUNTIF($B$1:B1,list),0))}</td></tr></tbody></table>Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself</td></tr></tbody></table>
<table rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" cellpadding="2.5px" width="85%"><tbody><tr><td style="padding:6px">Workbook Defined Names<table rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB" cellpadding="2.5px" width="100%"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Name</th><th style="text-align:left">Refers To</th></tr></thead><tbody><tr><th style=" background-color: #DAE7F5;color: #161120" width="10px">list</th><td style="text-align:left">=Sheet1!$A$2:$A$200</td></tr></tbody></table></td></tr></tbody></table>

 

[phester90]

Thank you Robert!

That worked amazingly. I just had one more question.
Is this possible to do for an entire notebook?

Each page is exactly the same and then on the last page I would love to make a unigue counter with all the dates and the frequency.

I appreciate your help and I am just learning excel so I am trying to learn as much as I can. Thank you for teaching me your wisdom.

-Phester90

 

[Robert Mika]

Probably not with formula.
You can do this exercise for each worksheet then in the Master Sheet copy all unique and past as values under each other( so you can have column of all unique from all sheets and then use formula like this:
http://www.excelforum.com/excel-general/792584-countif-across-multiple-sheets.html