Hello all
I am trying to workout number of students in age range group like 20-30, 30-40 and 40-50 based on a column "Student dob" . Can any one help your help and advice highly appreciated.
Hello,
Perhaps you could amend your thread title to something which does not assume what the solution will be?
Add a helper column to calculate the age of each student, eg.
where B4 contains the DOB and A1 contains today's date.=DATEDIF($B4,$A$1,"y")
Then add a pivot table, add the age column to the row labels and group by every 10 years. Then add the age column to the values field and set the aggregator to Count.
Hello Colin,
Thank you very much for your reply. Is there way I can get the required result without adding the column and pivot table. I mean I can use DATEDIF in conjunction with something COUNT or COUNTIF function. Your help will be highly appreciated.
Kaka
Hi Kaka,
Not a good way, no - and you can't do it with COUNTIF unless you use a helper column. If you don't want to use a helper column, you would have to use a SUMPRODUCT formula which is expensive. I've attached the previous workbook to show you how.
=SUMPRODUCT( --(DATEDIF($B$4:$B$32,$A$1,"y")>=$K3), --(DATEDIF($B$4:$B$32,$A$1,"y")<=$L3))
Also,
=SUMPRODUCT(($C$4:$C$32>=K3)*($C$4:$C$32<=L3))
Yes, but column C is a helper column for the pivot table.Originally Posted by nawas
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks