Conditional averaging

I'm using Excel 2000. I am only an occassional user of Excel with little experieince with its functions. I did statistics in my science degree 40 years ago.

I need help with an averaging problem for the column 'Average attendees' in the 'Activity categories' worksheet.

I have a spreadsheet with three worksheets: Attendance records, Activity categories, and Graphs.

In the Attendance records sheet I have six columns: Date, Activity, Category, No present, Community and Visitors.

Date, Activity, No Present (the total present) and Community (number of community members present) are entered from the attendance book.
Category is a reference to the Activity categories worksheet, for example, ='Activity categories'!B$8 which returns the value "Building maintenance".
Visitors is a calculated value =D2-E2 , that is, the difference between total number present and number of community members present.

In the Activity categories sheet I have four columns: ID, Category, No of uses and Average attendees

ID is an incremented value, for example, A4 is =A3+1.
Category is text, for example, Building maintenance
No of uses is a statistical function, for example, C8 (the Building maintenance category row) is =COUNTIF('Attendance records'!C$2:C$402,"Building maintenance") which gives the number of times the attendance record was for "Building maintenance".

My problem is the column Average attendees. For each category, I need to average the sum of values in the No Present column if the category column matches the Category of the row.

I have tried a logical function (IF, in which I test for the category then average):
=IF('Attendance records'!C2:C350,"Brigade call-out",(AVERAGEA('Attendance records'!D2:D350)))
which gives an "#VALUE!" error

I have tried a database function (DAVERAGE, in which I define the whole populated Attendance records worksheet as the database, the No Present column as the field and the IF test on the Category column as the criteria):
=DAVERAGE('Attendance records'!A1:F450,'Attendance records'!D2:D450,(IF('Attendance records'!C2:C450,"Brigade call-out")))
which also gives an "#VALUE!" error

Can anyone help me to get the average no present for each activity category, please?

I can provide a 31Kb copy of the spreadsheet with say ten rows in the Attendance records worksheet if required.

Try this formula which needs to be confirmed with CTRL+SHIFT+ENTER

=AVERAGE(IF('Attendance records'!C2:C350="Brigade call-out",'Attendance records'!D2:D350))

or to avoid CSE

=SUMIF('Attendance records'!C2:C350,"Brigade call-out",'Attendance records'!D2:D350)/MAX(1,COUNTIF('Attendance records'!C2:C350,"Brigade call-out"))

Thanks, daddylonglegs, it works a treat.

I used the SUMIF version, changed the row references to absolute with $ then copied down and changed the category labels for each row.

Have done the graphs of results as well and looks just what we wanted to get an overview of attendances.

I'm not sure exactly how it works, but will give that some more study when I'm not so pressed for time.

By the way, what was the significance of the CSE for the AVERAGE version? I hadn't seen that key-stroke combination before but it too worked when I tired it.