Count how many rows have the same date

[scott3294]

Hi,
Newbie here! I have a spreadsheet with rows and rows of data by date. The far left column houses the date and then each row is another occurrence for that date. Basically I need to count how many rows have the same date. Here is what the basic sheet looks like:

6/15/13
6/15/13
6/15/13
6/15/13
6/15/13
6/16/13
6/16/13
6/17/13
6/17/13
6/17/13

So the formula I am looking for would return, 6/15/13 =5, 6/16/13 = 2, 6/17/13 =3. Or something along those lines. I have a ton of data and using pivot table info to filter by each day will take FOREVER.
Any help is greatly appreciated.
Scott

[Fotis1991]

You can use Advanced Filter to get a unique list of the dates in a column.

Then a simple COUNTIF will do the job!

[daffodil11]

I'd copy your date column to another location Sheet2A1:A?, use Data -> Remove Duplicates to create a list of the unique values, then in =COUNTIF(Sheet1!$A$1:$A$5000,Sheet2!A1) and drag down alongside of each unique value

[scott3294]

Wow, thanks for the quick responses. Not sure I follow or I did not do a great job of explaining. I need to know how many times the date shows up in the sheet. If there are 15 rows with Dec 16, 2013 in them, I need a count to show 15. The challenge is I have so much data I cannot use advance filter and do that for each date. I was hoping to find a calculation that would scan the sheet and return the count of each data (how many times the date shows up). The calculation would return a simple number, 15. I was also hoping the calculation would run for the whole sheet and not just one date at a time. So I would get a result that showed how many times each date was listed. Every single day for the last six months is present in the sheet. Sometimes a day may be present 167 times, while the next day is present 22 times. I have started the process manually, but really would like to automate this.
Thanks for any idea
Scott

[Fotis1991]

.. The challenge is I have so much data I cannot use advance filter and do that for each date.

Advanced Filter is not working like this. You can use it from the control panel or using a simple macro..

See the example.

[daffodil11]

To accomplish this in a single cell without the use of VB, you would have to create an expression that compounded 167 other formulas.

It would come close to, if not exceed, the maximum character limit for a single formula. (I think it's a 2,000 character limit.)

[AlKey]

Please see attached file with formulas and layout.

[scott3294]

Thanks so much. These solutions worked perfectly. Again, thanks for taking time to help. It is much appreciated.
Scott

[Fotis1991]

You are welcome-From ALL of us- and thanks for the feedback.

As that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED,as per Forum Rule #9. Thank you.

Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.