Counting Dates in Excel 2

[Stewman]

Hello All,

Quick question.

I have a list of Dates in Column A:

27-May-02
28-May-02
28-May-02
28-May-02
28-May-02
28-May-02
29-May-02
29-May-02
30-May-02
30-May-02
1-Jun-02
1-Jun-02
1-Jun-02
1-Jun-02
2-Jun-02
2-Jun-02
3-Jun-02
3-Jun-02
4-Jun-02
6-Jun-02
6-Jun-02
7-Jun-02
8-Jun-02
10-Jun-02
10-Jun-02
10-Jun-02
12-Jun-02
12-Jun-02
13-Jun-02
16-Jun-02
17-Jun-02
19-Jun-02
21-Jun-02
21-Jun-02
23-Jun-02
23-Jun-02
23-Jun-02
23-Jun-02
23-Jun-02
27-Jun-02
28-Jun-02
28-Jun-02
29-Jun-02
29-Jun-02
29-Jun-02
30-Jun-02
30-Jun-02
30-Jun-02

I want to know the date that appears most but instead of the date being returned, I want to the number of times that date appears. In this list the 28-May-02 and 23-jun-02 appear 5 times. I want my cell to say 5. How do I do this? I know mode will return the value that appears most but I want the number of times it appears. Any hints greatly appreciated.

Chris

 

[ETID]

assuming that the dates are in col A

and your "criteria" date is in cell D1

in cell b1 ...

=COUNTIF(A:A,D1)

 

[TonyJollans]

Hi Stewman,

Combining ETID's COUNTIF with your MODE you get your answer ..

[blue]=COUNTIF(A1:A1000,MODE(A1:A1000))[/blue]

Change the range to suit, but note that MODE doesn't work on an entire column.

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.

 

[ETID]

Maybe the dcount() function would suit this best...


Do a search in the help it explains the database function nicely.

 

[Stewman]

Thanks ETID and TonyJollans. I appreciate your help. You answered my question perfectly.

Thanks, Chris