Excell 2000 IF Statement

[LPS]

I have a detailed list of training data which tracks course name, date,
instructor and 10 evaluation ratings (e.g.; how well were course objctives
met, how knowledgeable was the instructor, etc.). Each rating is a number
from 1 to 5. For example:

A B C D
E F G
Course Name Instructor Objectives Knowledge Equipement Registration
Ave.
Intro to Excel John Smith 2 1 1
2 1.5
Intro to Word Mary Wells 3 2 1
1 1.8

I have a separate summary sheet intended to summarise the course ratings per
instructor. For example, I need to calculate the overall course average for
each instructor and the number of courses taught by each instructor. Is there
a way to use the IF statement so that if the instructor's name is John Smith,
the average of all courses assigend to him is returned, along with the total
number of courses taught by him?

I hope I have provided enough information... Any help is greatly
appreciated. Thx,

 

[Joel]

To count the number of times John Smith occurs in column B use this formula

=COUNTIF($B:$B,"John Smith")

To get Average You need to get the total of all John smith's average and
then divide by the total from the formula above

=SUMPRODUCT(--(B1:B5="John Smith"),G1:G5)/COUNTIF($B:$B,"John Smith")