# number of times a date occurs

I have a spreadsheet that has dates i woul like ot have the dates  and number of times it occurs displayed
thanks
num-of-times.xlsb
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
Put this in G6 and copy down:

=COUNTIF(A:A,F6)

Flyster
0
Commented:
In cell G6, use this formula...

=COUNTIF(A3:A29, F6)

...where A3:A29 contains your list of dates and F6 contains the date to count.

If you want to automatically generate a table containing the individual dates with the count for each, use a Pivot Table....

1) Select cells A2:A29 (enter a heading in cell A2)
2) Insert a Pivot Table from the Insert tab of the Ribbon
3) Specify where you would like the table placed then click OK
4) From the Pivot Table field list, right click the heading you entered in cell A2 and select "Add to Row Labels"
5) Right click again and select "Add to Values".

By default it will display the count of each date in your table, as per the attached workbook.
C--Users-au-rns-manager-Desktop-num-of-t
0
Author Commented:
flyster that is not what i am looking for..i need the date and count not just the count.

thanks
0
Commented:
Use the Array Formula FREQUENCY()

See the attached file for your example.
FREQUENCY.xlsb
0
Excel VBA DeveloperCommented:
PivotTables!

Add a label in A2 ("Dates"), then insert a PivotTable on this same sheet in cell F5.  Make the Dates both the Row Labels and the Values (make sure it's the Count of Dates and not Sum of Dates).

Example file attached.

Regards,
-Glenn
EE-num-of-times.xlsx
0
Commented:
Here's an array formula with an IF statement to handle blank cells which will auto populate when a new date is entered.
num-of-times.xlsb
0

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Commented:
@Glenn Ray:   FACEPALM!   Of Course! Pivot tables!

@SvgMassive:  What Glenn Ray Said . . . .
0
Commented:
and Wayne before him... :)
0
Excel VBA DeveloperCommented:
^True Dat. +Wayne.
0
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.