# age entries after sort of column

[boomla]

Hi all,

I'm making a members list for a club, using Excel 2010.
The function I'm interested in is as follows:

I have a column containing birthdays of the members
below the column, I have a cell in which any date can be entered manually. This is the "final date"

Now, based on this "final date", I'd like to output (in an output cell) the amount of members who's age is 25 or less. (and another cell with age 26+)

I have tried the following:

Please Login or Register  to view this content.

where R5:R13 are the column containing the birthdays, and R16 containing the "final date".

I'm getting no errors in the function, but I get a total of 0 members in the output cell...
Is it correct to use COUNTIFS? - What else could/should I try to make this work?

Please do ask, if something isn't clear enough.

Thanks in advance for any constructive answer/suggestion!

[Haseeb Avarakkan]

Try this,

26+,

=SUMPRODUCT(--(EDATE(R16,-26*12)>=R5:R13),--(R5:R13>0))

<=25,

=SUMPRODUCT(--(EDATE(R16,-25*12)<=R5:R13),--(R5:R13>0))

[daddylonglegs]

You could use COUNTIF then you don't need the >0 check, e.g. for those who haven't yet reached their 26th birthday on R16 date

=COUNTIF(R5:R13,">"&EDATE(R16,-26*12))

[boomla]

Try this,

26+,

=SUMPRODUCT(--(EDATE(R16,-26*12)>=R5:R13),--(R5:R13>0))

<=25,

=SUMPRODUCT(--(EDATE(R16,-25*12)<=R5:R13),--(R5:R13>0))

First off - Im a bit surprised...
So short time, yet 2 suggestions - very nice

Secondly, Haseeb - you got the "most right" solution for me.
Meaning, that replacing the "," with ";" made the trick just right (using Excel 2010)

Thank you both, much appreciated!
boomla