Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    Jul 2002
    Location
    Winnipeg, Manitoba
    Posts
    40
    Thanks
    6
    Thanked 0 Times in 0 Posts
    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
    E. Fred Schneider
    someone who knows just enough to get themselves into trouble
    Winnipeg, Canada
    fred.schneider@sportmanitoba.ca

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    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.
    Attached Files Attached Files

  3. #3
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [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
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  4. #4
    Lounger
    Join Date
    Jul 2002
    Location
    Winnipeg, Manitoba
    Posts
    40
    Thanks
    6
    Thanked 0 Times in 0 Posts
    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
    E. Fred Schneider
    someone who knows just enough to get themselves into trouble
    Winnipeg, Canada
    fred.schneider@sportmanitoba.ca

  5. #5
    3 Star Lounger
    Join Date
    Dec 2006
    Posts
    250
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [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. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    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
  •