multiple criteria

[David J. Birnbaum]

Dear Excel Users,

Can someone advise me on how to count cell values according to multiple
criteria? Here are the details:

I administer a large university course with seven instructors. All
grades for the course are kept in a single spreadsheet, where the rows
are students and the columns are grades and other information about the
students. One column is the instructor's surname.

I want to track grading across instructors. I'd like to do this by
designing a table where the columns are the instructors' surnames and
the rows are the different letter grades. I then want to populate the
cells of the table with the number of A+, A, A-, etc. grades assigned by
each instructor.

I've used COUNTIF to find the number of, say, A+ grades in the entire
grades column, but I don't know how to use the column with the
instructor's surname as an additional criterion so as to extract only
the A+ grades that were assigned by a particular instructor.

Thank you for any advice.

Sincerely,

David Birnbaum
(e-mail address removed)

 

[Jim]

=SUMPRODUCT((Instructors="Jones")*(Grades="A+"))
where you have ranges named Instructors and Grades. Otherwise, substitute
the actual cell references for the named ranges.

 

[Steve]

You can create an array formula. Type in the following
formula and before exiting the field (or more
appropriately to exist the field) hit Cntl-Shift-Enter

=COUNT(IF(A2:A10="Jones",IF(B2:B10="A",0)))

Where Jones is the professor and A is the grade.

You should see the following in the task bar

{=COUNT(IF(A2:A10="Jones",IF(B2:B10="A",0)))}

(note the extra brackets)

Regards,

Steve