Criteria Between two ages problem

Hi,

I have searched the forums to see if there is a similar prob already answered but to no avail (apologies if this has already been answered).

I am designing a sheet for a Uni project that needs to calculate the average attendance rates for students between age groups. My data has the age in a specific age for example:

Age
18
19
19
21

etc.

I am trying a SUMIF function that adds all attendance rates based on a criteria - however I can't get the criteria to fall between two ages ranges (more specifically, I need to sum the averages where the ages is between 21 - 25), as the function seems to only allow for one expression of criteria.

My function currently Reads:
=SUMIF(A1:A25, ">=21 & <=25", B1:B25)

Where A is the column recording ages and B is the column recording attendance rates. It presently returns 0.

Is there a better function for this task, or a way around the SUMIF criteria?

Any help would be greatly appreciated!

Thanks for your quick response!

I checked the thread, and if I'm reading it correctly this will return the number of data entries that match the age range. I need it to Sum the attendance rates where the age is between two values.

The purpose then being I can divide this number by the total frequency of records with this age range - which I have calculated with the below formula:

=SUMPRODUCT((A1:A25 >=21)*(A1:A25 <=25))

And this final sum will then return the average attendance rate for students aged between 21 - 25.

I possibly mis read and don't understand the other post - is this what it will return?

Please let me know if I'm not making any sense (it happens frequently!)

I've entered the formula into my spreadsheet - applying changes where necessary and I get #VALUE! returned. I must admit to not being completely clear about the formula, thank you for your help so far - it is really appreciated!

Thanks for the new conversion!

I applied this to my sheet and it returns a value of 134.

Using advanced filters I have determined the value will be 458.3 - very strange! Would it help if I posted the data?

Sorry about the delay with this post:

Please find data enclosed below:
Age (Yrs) Attendance Rate (%)
18 ----------- 50
18 ----------- 33.3
19 ----------- 66.7
19 ----------- 100
21 ----------- 100
20 ----------- 100
19 ----------- 80
25 ----------- 75
21 ----------- 100
19 ----------- 66.7
31 ----------- 100
22 ----------- 83.3
21 ----------- 66.7
19 ----------- 60
18 ----------- 57.14
19 ----------- 66.7
20 ----------- 50
24 ----------- 33.3
19 ----------- 20
18 ----------- 75
19 ----------- 25
19 ----------- 50
20 ----------- 66.7
19 ----------- 83.3
18 ----------- 83.3


My age groups are
18-20
21-25
26 +

Using filters I have determined that these should return the following values with a correct formula:

Group----Sum of Attendence------------Av. attendence
18 - 20 = 1133.8 ----------------------- 63%
21 - 25 = 458.3 ------------------------ 76.4%
25 + = 100 ---------------------------- 100%

The formula you provided is as follows:
=SUMPRODUCT(--($A$1:$A$25<=25),--($A$1:$A$25>=21),$A$1:$A$25)

and returns the value 134.

I'm not sure how to proceed - once again thanks for your kind help!

I've just figured out the problem - you were right with the second formula, but the column was mis placed (probably by my description!)

The correct formula is:
=SUMPRODUCT(--($A$1:$A$25<=20),--($A$1:$A$25>=18),$B$1:$B$25)

Thank you so much - I've spent over a week trying to figure this one out!