Countif function question

[stevestr]

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

 

[JoeU2004]

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)*(datedif(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 -----

 

[smartin]

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.