Solved

# number of times a date occurs

Posted on 2014-12-18
61 Views
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
0
Question by:Svgmassive
• 2
• 2
• 2
• +3

LVL 22

Expert Comment

ID: 40508408
Put this in G6 and copy down:

=COUNTIF(A:A,F6)

Flyster
0

LVL 47

Expert Comment

ID: 40508410
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 Comment

ID: 40508418
flyster that is not what i am looking for..i need the date and count not just the count.

thanks
0

LVL 1

Assisted Solution

dm_osborn earned 250 total points
ID: 40508433
Use the Array Formula FREQUENCY()

See the attached file for your example.
FREQUENCY.xlsb
0

LVL 27

Expert Comment

ID: 40508447
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

LVL 22

Accepted Solution

Flyster earned 250 total points
ID: 40508459
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

LVL 1

Expert Comment

ID: 40508461
@Glenn Ray:   FACEPALM!   Of Course! Pivot tables!

@SvgMassive:  What Glenn Ray Said . . . .
0

LVL 85

Expert Comment

ID: 40508823
and Wayne before him... :)
0

LVL 27

Expert Comment

ID: 40509401
^True Dat. +Wayne.
0

## Featured Post

### Suggested Solutions

What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…