Hey,
I have a list of Date of Births, and another table that categorises the DOBs into age ranges. I'm using the formula e.g. =SUMPRODUCT(--(DATEDIF(B3:B16,TODAY(),"Y")<18)) for age ranges below 18.
But for 65 years and over, the formula =SUMPRODUCT(--(DATEDIF(B3:B16,TODAY(),"Y")>64)), seems to also count blank cells. Is there a way to stop it from counting blank cells? I've attached the spreadsheet for reference.
Thanks!
T
Have you tried frequency function? And it doesn't take blank into consideration when you do the counting
http://support.microsoft.com/kb/100122
Jie,
Thanks for the tip. But how would you embed the SUMPRODUCT into the FREQUENCY function?
Oh never mind, I missed the attachment
Is there a way of doing this without putting a new column in for Age? I'm working with a spreadsheet that I can't put new columns in for.
Change your formula to
=FREQUENCY(DATEDIF($B$3:$B$15,TODAY(),"y"),{17,24,19,34,39,44,49,59,64})
Great- thanks!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks