Countif function question

  • Thread starter Thread starter stevestr
  • Start date Start date
S

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
 
stevestr said:
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.
Click to expand...

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 -----
 
Back
Top