Count if age between age range at specific date

[M4rk]

I have an existing formula that returns the number of active members attending various activities for a given month using the following countifs formula:

=COUNTIFS(KAMembers[[#All],[Sep2]],">2012/09/01",KAMembers[[#All],[Clubs 1]],Lists!$G$2,KAMembers[[#All],[Gender]],"F")

I would like to add another criteria to the formula to narrow results even further by age range 11-13, 14-16, 17-19 etc as at the last day of each month.

My mind has gone blank and nothing I try works!

The workbook contains a membership worksheet (KAMembers) and individual worksheets for each month (Sep2 etc) to record attendances.

 

[chataylo]

You should be able to do that easily using the same formatt that you have. You have to add a column witht he members age, then add additional criterea to your count if statement saying if age is greater than this and less than this.
The criterea is typed in using the following format:

Range,">11",Range,"<13" for the ll-13 group.

 

[M4rk]

Thanks chataylo, I took your advice and created additional columns to return everyone's ages at each month end and although it's been a bit of a pain to modify the formula for each cell in my report it seems to work fine and returns the individual attendances by age range and gender. Here's the modified formula

=COUNTIFS(KAMembers[[#All],[Sep]],">=2012/09/01",KAMembers[[#All],[Clubs 1]],Lists!$G$2,KAMembers[[#All],[Gender]],"M",KAMembers[[#All],[Age at Sept]],">=11",KAMembers[[#All],[Age at Sept]],"<=13")

 

[chataylo]

Appreciate the feed back.

chataylo

Thanks chataylo, I took your advice and created additional columns to return everyone's ages at each month end and although it's been a bit of a pain to modify the formula for each cell in my report it seems to work fine and returns the individual attendances by age range and gender. Here's the modified formula

=COUNTIFS(KAMembers[[#All],[Sep]],">=2012/09/01",KAMembers[[#All],[Clubs 1]],Lists!$G$2,KAMembers[[#All],[Gender]],"M",KAMembers[[#All],[Age at Sept]],">=11",KAMembers[[#All],[Age at Sept]],"<=13")