Results 1 to 11 of 11

20070304, 17:59 #1
 Join Date
 Jan 2007
 Location
 Gray, Louisiana, USA
 Posts
 289
 Thanks
 0
 Thanked 0 Times in 0 Posts
Counting Dates that Occur in a Particular Year (2003)
I have a spreadsheet with dates in column E that span from 2004 through 2007.
I need a formula to count the number of dates in the list for each year. (There are over 1500 dates in the list)
I have named the range rptdates and would like to use this in the formula.
I have in another sheet in the same workbook, listed the years across columns 2004,2005,2006,2007and would like the result/count to be in the cell below each year
Any help/ideas would be greatly appreciated.

20070304, 18:41 #2
 Join Date
 Feb 2002
 Location
 A Magic Forest in Deepest, Darkest Kent
 Posts
 5,681
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Counting Dates that Occur in a Particular Year
Hi There
You could try:
{=SUMPRODUCT((A1:A100>=J1)*(A1:A100<J2))} where J1 = 1/1/2006 and J2 = 31/12/2006 and press Ctrl +Shift + Enter for an array formula you
adjust range to fitJerry

20070304, 19:14 #3
 Join Date
 Jan 2007
 Location
 Gray, Louisiana, USA
 Posts
 289
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Counting Dates that Occur in a Particular Year
Thanks it worked great, I didn't use CTRLSHIFTENTER though. It still worked thanks again

20070304, 19:15 #4
 Join Date
 Feb 2002
 Location
 A Magic Forest in Deepest, Darkest Kent
 Posts
 5,681
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Counting Dates that Occur in a Particular Year
An alternative is to create a column next to the date and use the formula =Year(A1). You could then do a pivot table.
1) Highlight new column
2) Select Data  Pivot Table and Pivot Chart Report
3) Next
4) Confirm Range
5) Next
6) Click Layout
7) Drag the year into Column and then again Data (don't worry it will say sum...we will change that later)
8) Click Finish
With the pivot table active (mouse click in) Click Pivot Table Menu and select:
9) Field Settings and select Count
10) Press OK
You now have a column counted list of years..........enjoyJerry

20070304, 23:45 #5
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,286
 Thanks
 3
 Thanked 194 Times in 180 Posts
Re: Counting Dates that Occur in a Particular Year
Jerry,
SUMPRODUCT is designed to work with arrays like that so doesn't need to be array entered here. FWIW.Regards,
Rory
Microsoft MVP  Excel

20070304, 23:51 #6
 Join Date
 Feb 2002
 Location
 A Magic Forest in Deepest, Darkest Kent
 Posts
 5,681
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Counting Dates that Occur in a Particular Year
Cheers Rory that is good to know, I suppose it was just habit <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
Jerry

20070305, 11:25 #7
 Join Date
 Jan 2007
 Location
 Gray, Louisiana, USA
 Posts
 289
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Counting Dates that Occur in a Particular Year
I tried the pivot table suggestion but I didn't get past the Year formula it returned the wrong date. I made sure all the cells in the columns where the original date is and the formula is were Date Formattedsee below: The Year formula returns the results in the column to the right and references the column to the left.
1/5/2004 6/26/05
1/2/2002 6/24/05
1/8/2002 6/24/05
1/9/2002 6/24/05
1/14/2002 6/24/05
1/2/2002 6/24/05
1/4/2002 6/24/05
1/4/2002 6/24/05
1/4/2002 6/24/05
1/5/2002 6/24/05
1/8/2002 6/24/05
1/11/2002 6/24/05
1/12/2002 6/24/05
1/16/2002 6/24/05
1/16/2002 6/24/05
1/21/2002 6/24/05
1/15/2002 6/24/05
1/22/2002 6/24/05
I tried this in a new workbook and the Year formula worked, but in the workbook where I want to use it I get the above results. This workbook could have been created in an older version of Excel. Would that matter?

20070305, 11:26 #8
 Join Date
 Feb 2002
 Location
 A Magic Forest in Deepest, Darkest Kent
 Posts
 5,681
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Counting Dates that Occur in a Particular Year
Hi There
How strange, I have added this workbook for you to see how I did it.Jerry

20070305, 11:32 #9
 Join Date
 Jan 2007
 Location
 Gray, Louisiana, USA
 Posts
 289
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Counting Dates that Occur in a Particular Year
Thanks, I'll try this and let you know.

20070305, 12:08 #10
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,286
 Thanks
 3
 Thanked 194 Times in 180 Posts
Re: Counting Dates that Occur in a Particular Year
Hi,
Those results are actually correct but the cells are formatted as dates. Excel treats dates as number of days since 31/12/1899  so where your year formula returns 2004, if the cell is formatted as a date, Excel counts 2004 days from 31/12/1899 which gives you 26 Jun 1905. If you format the cell as General you will see 2004 etc.
HTHRegards,
Rory
Microsoft MVP  Excel

20070306, 00:21 #11
 Join Date
 Jan 2007
 Location
 Gray, Louisiana, USA
 Posts
 289
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Counting Dates that Occur in a Particular Year
That's good info and it was exactly the problem thanks to all. I've been working so I haven' t had a chance to try the Pivot yet but I will and I will let you know how it goes thanks.