# Thread: count # of records per year

1. Hi all. and thanks in advance. This forum has proven invaluable previously and I'm expecting the same again.

I have what appears to be a simple task. I need to count the number of records associated with a particular year from a "database" in excell. There are over 21000 records and one of the "fields" is created date which is entered as a date from 1/1/2006 through 7/7/2009. I need to know the number of records with a created date in 2006, 2007, 2008, & 2009. Can I use a countif statement? or do I need to look at sum, with if statements.

Not sure how to proceed to get only those records for each particular year.

Any help to this still learning newbie is appreciated. Thanks for all the past help and for the anticipated help on this one. All contributors here rock. Here's to each and every one of you

Fred

2. One option is to use array formulas. For example, with the years 2006 through 2009 in D25, you can enter the following formula in E2 as an array formula (confirm with Ctrl+Shift+Enter):

=SUM(1*(YEAR(\$A\$2:\$A\$101)=D2))

Fill down to E5.

Another option is to create a pivot table based on your dates, and to group the date field by year.
A pivot table isn't updated automatically, you have to click the Refresh Data button on the Pivot Table toolbar or select Data | Refresh Data.

Both are illustrated in the attached workbook.

3. [quote name='derfacanuck' post='783372' date='07-Jul-2009 23:52']Hi all. and thanks in advance. This forum has proven invaluable previously and I'm expecting the same again.

I have what appears to be a simple task. I need to count the number of records associated with a particular year from a "database" in excell. There are over 21000 records and one of the "fields" is created date which is entered as a date from 1/1/2006 through 7/7/2009. I need to know the number of records with a created date in 2006, 2007, 2008, & 2009. Can I use a countif statement? or do I need to look at sum, with if statements.

Not sure how to proceed to get only those records for each particular year.

Any help to this still learning newbie is appreciated. Thanks for all the past help and for the anticipated help on this one. All contributors here rock. Here's to each and every one of you

Fred[/quote]

Hi

To answer your question, yes, you can use the COUNTIF formula like this
Assuming your Create Dates are in Col A

In C2, enter this for the year 2006
=COUNTIF(\$A\$2:\$A\$101,">="&DATE(2006,1,1))-COUNTIF(\$A\$2:\$A\$101,">"&DATE(2007,1,1))

In C3, enter this for the year 2007
=COUNTIF(\$A\$2:\$A\$101,">="&DATE(2007,1,1))-COUNTIF(\$A\$2:\$A\$101,">"&DATE(2008,1,1))

In C4, enter this for the year 2008
=COUNTIF(\$A\$2:\$A\$101,">="&DATE(2008,1,1))-COUNTIF(\$A\$2:\$A\$101,">"&DATE(2009,1,1))

In C5, enter this for year 2009
=COUNTIF(\$A\$2:\$A\$101,">="&DATE(2009,1,1))-COUNTIF(\$A\$2:\$A\$101,">"&DATE(2009,7,7))

the last formula will count up to 7/7/2009 as specified, if you want to count for the full year of 2009
change the Date(2009,7,7) to Date(2010,1,1)

I tried using cell's references but its doesn't works.
If your dates will grow infinitely, Pivot Table is the way to go as have been demonsrated by Hans

Hope this help

4. thanks for the responses. They have proved invaluable.

Hans... you've helped me out previously.... and once again you came through. It is appreciated.

I'm now

Thanks again for the quick responses. I have learned something today that I will be able to use in the future...

Fred

5. [quote name='HansV' post='783376' date='07-Jul-2009 16:04']One option is to use array formulas. For example, with the years 2006 through 2009 in D25, you can enter the following formula in E2 as an array formula (confirm with Ctrl+Shift+Enter):

=SUM(1*(YEAR(\$A\$2:\$A\$101)=D2))

Fill down to E5.

Another option is to create a pivot table based on your dates, and to group the date field by year.
A pivot table isn't updated automatically, you have to click the Refresh Data button on the Pivot Table toolbar or select Data | Refresh Data.

Both are illustrated in the attached workbook.[/quote]

Hans, can I ask how you got " <03-01-2006" and ">01-01-2010" fields in the pivot table in the attachment. This would be great to know since everytime I do a pivot table it list all the values if that is the field that I selected. Thanks.

6. Those limits were entered automatically by Excel itself, based on the extent of the source data...

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•