You may want to take a look at the SUMPRODUCT function.

"Gupta A." wrote:

If I have a class of students, say A-F and they have different grades in
different subjects. Everytime they take a new test I give them a new grade
and I keep an average of all the grades for each student. In a ranking table
I use a macro to sort the grade of the students in ascending order. They have
grades for multiple subjects so I have multiple rankings. Now if I want to
give them one point for being in the lowest of a group, and 6 for being the
highest of the group and I wish to sum up all these rankings to find out waht
their average ranking is - well one way is the countif function, and for
every row I count the number of times that name appears and multiply it by
the number of poitns I wish to award. However if I have more than a hundred
students that becomes impractical. Is there a way to count the number of
times a name appears in a row and to multiply it by a number in a quicker
way? is there a better way to do this?