If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Countif function question
I have a list of birth dates in column A. I want to provide a list of age
ranges in a summary next to the column. For example I would like to create a function that would give me the count of birthdates in column A that show the number of listings that are less than 2 years old. Then I would like to show the count of listings that are older than 2 years but less than 10 years. And finally I would like to show the count of listings that are older than 10 years. I would like for the calculation to include the function that would keep the counts current. This way as I keep this data over the next few years, I won't have to update the forumula. I found a few countif threads that touched on portions of this question but I didn't know how to make it work. I'm using 2007. Thanks |
#2
|
|||
|
|||
Countif function question
"stevestr" wrote:
I found a few countif threads that touched on portions of this question but I didn't know how to make it work. I'm using 2007. You could use COUNTIF or, in Excel 2007, COUNTIFS. But I believe that would require a helper column. Alternatively: C1: =sumproduct(--(datedif(A1:A1000,today(),"y")2)) C3: =sumproduct(--(datedif(A1:A1000,today(),"y")10)) C2: =counta(A1:A1000)-C1-C3 Note: C2 counts the number age 2 or more and age 10 or less, not "older than 2" and "less than 10". The latter creates a discontinuity: you neglect to count those that are age 2 and age 10. If that is your intent, C2 could be written as: =sumproduct((datedif(A1:A1000,today(),"y")2)*(dat edif(A1:A1000,today(),"y")10)) Caveat emptor: if the birthday is 2/29/2008 and TODAY() is 2/28/2010, DATEDIF returns 1, not 2. Is that a problem? ----- original message ----- "stevestr" wrote in message ... I have a list of birth dates in column A. I want to provide a list of age ranges in a summary next to the column. For example I would like to create a function that would give me the count of birthdates in column A that show the number of listings that are less than 2 years old. Then I would like to show the count of listings that are older than 2 years but less than 10 years. And finally I would like to show the count of listings that are older than 10 years. I would like for the calculation to include the function that would keep the counts current. This way as I keep this data over the next few years, I won't have to update the forumula. I found a few countif threads that touched on portions of this question but I didn't know how to make it work. I'm using 2007. Thanks |
#3
|
|||
|
|||
Countif function question
stevestr wrote:
I have a list of birth dates in column A. I want to provide a list of age ranges in a summary next to the column. For example I would like to create a function that would give me the count of birthdates in column A that show the number of listings that are less than 2 years old. Then I would like to show the count of listings that are older than 2 years but less than 10 years. And finally I would like to show the count of listings that are older than 10 years. I would like for the calculation to include the function that would keep the counts current. This way as I keep this data over the next few years, I won't have to update the forumula. I found a few countif threads that touched on portions of this question but I didn't know how to make it work. I'm using 2007. Thanks For a start, see Chip Pearson's exposé on the practically abandoned DATEDIF function: http://www.cpearson.com/excel/datedif.aspx You are not clear what to do with "number of listings that are /equal to 2 years old/" (similarly for /equal to 10 years old/), but these are merely boundary conditions to be worked out. Post back if you get stuck. |
Thread Tools | |
Display Modes | |
|
|